+ Reply to Thread
Results 1 to 3 of 3

conditional formatting in pivot using calculated measures

  1. #1
    Registered User
    Join Date
    12-02-2008
    Location
    London
    Posts
    51

    conditional formatting in pivot using calculated measures

    Hello
    I struggle with conditional formatting at the best of times but here I have a large pivot table with calculated power query measures and I would like to conditionally format each headline % column based on whether calculated difference is greater than (green fill) or less than (red fill) confidence interval.

    I've attached a sample workbook. In my finished worksheet I would like to hide those columns used for conditional formatting (the 'difference' and the Confidence Interval) and just show the formatted headline % column. Does anyone have an idea of how best to achieve this?

    Many thanks,

    A
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-02-2008
    Location
    London
    Posts
    51

    Re: conditional formatting in pivot using calculated measures

    just an update - i have created another measure which says if diff > CI then 1 or if diff < -CI then 2. So now I have a column of 1s, 2s and blanks.
    A straightforward conditional format question:

    what is the formula i need in 'use a formula to determine which cells to format' to colour fill my % column based on value in my cond format measure (1 = green and 2 = red). Then I can apply this formatting to relevant columns and hide my ref columns.

    Thanks,
    A
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: conditional formatting in pivot using calculated measures

    For the samples provided try the following formulas:
    For Green: =F9>H9
    For Red: =F9<-H9
    Both formulas are applied to: =$E$9:$E$12,$I$9:$I$12
    Note be sure that cell E9 is the active cell when conditional formatting is selected.
    Note that cell I9 (file attached to post #2) is not highlighted as -5 is not less than the negative of cell L9 (5). If conditional formatting should highlight cell I9 then for red then change the formula to read: =F9<=-H9
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help needed with fx Measures (Calculated fields) with Power Pivot
    By redalert787 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-01-2020, 09:49 PM
  2. How to Put Text Into the Measures in a Pivot Table
    By alansidman in forum Tips and Tutorials
    Replies: 0
    Last Post: 05-15-2020, 05:07 PM
  3. [SOLVED] PowerPivot - problems adding multiple calculated fields (measures)
    By G C in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2017, 11:06 AM
  4. [SOLVED] Data Bar (Conditional Formatting) reflects only 0%, 50%, and not the calculated value?
    By Excel_Ghost in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2015, 12:09 PM
  5. Formatting - Calculated Field in Pivot Table.
    By thabounceisback in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-01-2013, 11:04 AM
  6. [SOLVED] Formatting calculated totals in a Pivot Table
    By overcanyon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2005, 02:25 PM
  7. [SOLVED] Conditional Formating on calculated data in a pivot table
    By David in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1