Conditional formatting

[ananth]

I have a pivot table, and want to conditionally format the values in it with different shades of green. Lighter shades for small values and vice versa. How do I do this?

Thanks!

[RafaelBarrios]

Hi @ananth 

a bit manual as it can be tricky on pivot tables, but will do the trick

you can use background color expression option

whi something like this

if(column(1) < 60000, argb(10,26,255,0),

  if(column(1) < 120000, argb(100,26,255,0),

    if(column(1) < 240000, argb(150,26,255,0),argb(255,26,255,0))))

changing the first value of the ARGB function

Hope this helps.
best regards,

[Lech_Miszkiewicz · Accepted solution]

Hi, 

I would not use hardcoded option. Instead depending on the layout of your table I would use Rank/RowNo(Total) or HRank/ColumnNo(Total) or other function (like example below) to create values between 0 and 1 and use them inside of ColorMix() function.

For example:

ColorMix1(1-Max( Aggr(Rank(Total [YourMeasure]),Dim1,Dim2))/Max(Total Aggr(Rank(Total [YourMeasure]),Dim1,Dim2)) ,white(),green())

[RafaelBarrios]

Yeap, @Lech_Miszkiewicz approach is definitely better.

I was trying to give an initial, easy-to-understand idea.

But we already know that temporary solutions often end up becoming permanent.


best regards,

[Chanty4u]

May be you can try this 

If(

    Max(TOTAL Sum(Sales)) = Min(TOTAL Sum(Sales)),

    LightGreen(),

    ColorMix1(

        (Sum(Sales) - Min(TOTAL Sum(Sales))) /

        (Max(TOTAL Sum(Sales)) - Min(TOTAL Sum(Sales))),

        LightGreen(),

        DarkGreen()

    )

)