Coloring the Max and Min Values in Pivot Table Rows with Multiple Dimensions

[mrthomasshelby]

Hello,

I have a table as shown in the image below

This table has the option of choosing multiple dimensions from a multi box named 'Columns/X-axis'. Now what I want is to color the minimum and maximum values in each row of the table. For example, say in the first row(Malaysia,Peninsular,State1) Supplier A has the least value(273) and Supplier E has max value(325). Say I'd like to color the least value(273) as green and max value(325) as red. I want to do this for all the rows in the table. Any help is much appreciated. I have tried the following expression in the Background option for the 'AVG' expression but it's not working:

if(Avg($(v_MP_Rate_Type))=Max(TOTAL <[Supplier Name]> $(v_MP_Rate_Type)),RGB(255,0,0))

I have attached the app for your reference. TIA! stalwar1‌

[fvelascog72 · Accepted solution]

Hi,

Try with this in background color of the expression:

If(AVG = Min(TOTAL <$(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), Red(),

If(AVG = Max(TOTAL <$(v_MP_Dim_3)> Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))), blue()

))

Saludos

[YoussefBelloum]

Hi fvelascog72‌,

I tried to do it with nested aggr, do you have an idea why it doesn't work like this ?

[fvelascog72]

Hi,

You mean nested aggr instead nested if?

[YoussefBelloum]

Yes

[mrthomasshelby]

That works! Thanks a lot Federico! Can you explain briefly the rationale behind this expressions if possible? Thanks again!

[fvelascog72]

Could you write here what are you trying?

[fvelascog72]

With the aggr you select all values and with the TOTAL <$(v_MP_Dim_3)> you choose row by row. More or less?

[YoussefBelloum]

EDITED

First, i was trying this:

if(AVG = Min(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3)))...

but on the table I have only one max value and one min value of all the lines.

and you added another aggregation on the dimension 3, with total <dimension 3>, I want to know why ?

and so I tried this, but it is not working:

if(AVG = aggr(Min(Aggr(Avg($(v_MP_Rate_Type)), [Supplier Name], $(v_MP_Dim_1), $(v_MP_Dim_2), $(v_MP_Dim_3))),$(v_MP_Dim_3)), Red())

[mrthomasshelby]

Hello Federico, I'm getting the result as shown in the image. As you can see, the coloring is happening for some rows but not for some other rows. For example you can see in Region 'East','State 3' only the Max value is colored while the Min value is not. Similarly, in Region 'Peninsular', only Min is colored and not max. This is happening because State 3 is present in both the regions I guess. Is there anyway we can modify this expression to get the two colors for each row. For example, in Region 'Peninsular', 'State 3' Supplier A should be colored in Red while Supplier E should be colored in blue? Thanks again!