Highlight Min, Max and median numbres in table/pivot with colors

[richard24best]

Hi All,

Is it possible to highlight Min, Max and Median value with different colours in a table/ Pivot?

Request you to please check and advice. screenshot attached for your reference

my measure is count([CTMP CPTY Reason])

Appreciate your assistance.

Thanks,

Richard

[Kushal_Chawda · Accepted solution]

You can use below expression in background color of your measure. Where Dimension1,Dimension2  are pivot table dimensions. You can fill the RGB color values based on requirement

=if( count([CTMP CPTY Reason]) = max(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,),

if( count([CTMP CPTY Reason]) = min(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,),

if( count([CTMP CPTY Reason]) = median(total aggr(count([CTMP CPTY Reason]),Dimension1,Dimension2)), rgb(,,,))))

[richard24best]

Hi Kush,

Thanks for your quick support.

I tried as per your suggestion, not sure why only #2 are getting highlighted. I used expression as below -

=if(count([CTMP CPTY Reasons]) = max(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), red(),

if(count([CTMP CPTY Reasons]) = min(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), green(),

if(count([CTMP CPTY Reasons]) = median(total aggr(count([CTMP CPTY Reasons]),[CTMP Joint Touch Statement],[CTMP Market Country])), blue ())))

Thanks in advance , please advice if I am missing something above. screenshot attached.

Regards,

Richard

[Kushal_Chawda]

looks like you have 3 dimensions in pivot table so in aggr you need to include all the dimensions

[richard24best]

No I just have 2 dimensions -

[CTMP Joint Touch Statement],

[CTMP Market Country] and one measure

count([CTMP CPTY Reasons]

[Kushal_Chawda]

what is Touched Trades?

[richard24best]

That is nothing but CTMP joint touch statement dimension which I have renamed to touched trades 🙂 

[Kushal_Chawda]

What is capacity indicator, coupon rate amount then?

[richard24best]

capacity indicator, coupon rate amount, etc are the reasons listed under CTMP CPTY reasons which we are counting under measure against 2 dimensions 

[Kushal_Chawda · Accepted solution]

So you need to include CTMP CPTY reasons  as well in aggr