Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I want to highlight values in a pivot table which is build as follows:
lines: different weight bands
columns: different destintations/zones
I currently use the following formula:
ColorMix2( (rank(total column(1))/(noofrows(TOTAL)/2))-1 ,rgb(0,70,155), rgb(204,224,238),rgb(242,242,242))
This one is only referencing on each single row.
I want it to reference on the whole matrix, so that eg. zone 5 / weight 301-400kg = 5 and zone 6 / weight 801-900kg = 5 are shown with the same background color.
Which expression do I need to use for that?
Thank you. this is working.
One minor thing:
Now it is reffering to all weight bands existing in the data set.
When I filter on specific values, the colors stay the same.
Can that be adjusted as well? Maybe with {$}?
If there is no set analisys, the default is {$}. Maybe I'm missing something but the only reason I see now is that the selected values includes the max cell value, if the value remains the same the % that assignes the color returns the same result.
But maybe you are looking for something based on rank like:
ColorMix1(Aggr(Rank(TOTAL Count([Shipment no]),1,1), D1, D2)/Max(Aggr(Rank(TOTAL Count([Shipment no]),1,4), D1, D2)), rgb(204,224,238),rgb(0,70,155))
This only gives me the rgb(0,70,155) in all cells if I set
[Weight Band] for D1 and
[Zone] for D2
Yep, should be a TOTAL in the max:
/Max(TOTAL Aggr(Rank(TOTAL Count([Shipment no]),1,1)
And maybe colors have to be switched.
Thanks,
but with...
ColorMix1(Aggr(Rank(TOTAL Count([Shipment no]),1,1), [Weight Band], [Zone])/
Max(TOTAL Aggr(Rank(TOTAL Count([Shipment no]),1,4), [Weight Band], [Zone])),
rgb(0,70,155),rgb(256,256,256))
I get:
Columns 7 and 8 each show a "2", One is white, the other is blue-ish?!
Best option is to split the expression in two, and check if the values in each part of the division are the expected values.
Parameters for Rank should be the same in both parts of the division. (1,1 or 1,4)