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]

youssef belloum escribió:

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

That´s why you must choose row by row with total <dimension 3>.

I think doing this in pivot tables is risky because it will not work if you pivot the dimensions.

If you search other option tell me. Always learnig.

[fvelascog72]

Try with:

If(AVG = Min(TOTAL <$(v_MP_Dim_2), $(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_2), $(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()

))

[YoussefBelloum]

Thank you,

I am not yet very comfortable with this kind of problematics..

It would be very interesting to have the advice of one of the experts here, like stalwar1‌, what would you have used here Sunny ?

[mrthomasshelby]

This is perfect! I understand now that we're considering the cells based on vDim2 and vDim3. Thanks a lot for all the help again Federico! Cheers!

[fvelascog72]

You´re welcome

[sunny_talwar]

I will try to get back to you on this sometime today

[YoussefBelloum]

Merci Sunny

[sunny_talwar]

I think you might have been trying to do this.... right?

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

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

))

youssef‌ - Apologize for the delay

[YoussefBelloum]

Yes, thank you so much.. I was trying with Aggr only and it didn't work..

it is perfect

You know me I love the details, so I'll ask a question here too:

between the solution of federico: Min(TOTAL <$(v_MP_Dim_3)> ..aggr(avg

and yours: Aggr(NODISTINCT Min(Aggr(avg

both are the same ? in terms of performance maybe ?

[sunny_talwar]

I like using TOTAL <> instead of Aggr(NODISTINCT and more or less they give the same output.... but more recently I have come across some examples where TOTAL <> doesn't work and I have had to fall back to Aggr(NODISTINCT...).

So, my first attempt is usually to get it work using TOTAL <>... if that doesn't work, I fall back to Aggr(NODISTINCT...)