Pivot Table Background Coloring when Expanded Only

[Anonymous]

Hello everyone!

I have been working with Pivot Tables lately, and I was asked to color some pivot tables cells based on a measure value. For example:

Having the following table:

ID               ,                  Name              ,    Duration         

1                ,               Joe                    ,   10

2                ,              Lee                     ,   11

3               ,               Joe                    ,    12

4                ,              Lee                     ,   13

5                ,               Joe                    ,   14

6                ,              Lee                     ,   15

7               ,               Joe                    ,    16

8                ,              Lee                     ,   17

Now every duration greater or equals 14 should be colored red, otherwise green, and here is my expression:

if(Duration >= 14, red(), Green() ) )

My issue is when the table is collapsed, the sum of Joe and Lee will be RED. What I want is for the coloring to appear when expanding the table only.

Thanks!

[jonathandienst · Accepted solution]

Use Dimensionality() to apply the colour coding to the bottom level expressions only.

     =If(Dimensionality() = 3 And Duration >= 14, Red(), Green())

[jonathandienst]

Or if you want the colour coding to work based on the largest value and rolled up to the higher dimension values:

     =If(Max(Aggr(Duration, ID, Name)) > = 14, Red() , Green())

If Duration is an expression, then replace the "Duration" in the above expression with the Duration expression.

[rahulpawarb]

Hello Feras,

Trust that you are doing well!

For Duration, define the Background Color expression as If(Dimensionality()=2,if(Duration >= 14, red(), Green()))

Also refer the attached application file.

Hope this will be helpful.

Regards!

Rahul

[Anonymous]

Thanks!

[Anonymous]

Thanks!

[sharmi1990]

Why should the dimensionality() be = 3?