ColorMix gradient in pivot chart for first dimension

[mats_kroky]

Hej,

I'm struggling with getting a rank colour background for the first dimension in pivot chart. 

What I would like to achieve is that 'inside' country dimension (first one) the ratio between value1 and value2 is rank from green (lowest) to red (highest). I came up with the below formula in background colour field but I'm not getting the result I'm after. 

ColorMix1( rank(total column(1))/NoOfRows(TOTAL) , lightred(), lightgreen())

And column Ratio% is calculated as follow: sum(value2)/sum(value1)

[Gui_Approbato]

Try this and see if it works:

ColorMix1(  (Sum(Value2)/Sum(Value1))/

Max(Total Aggr((Sum(Value2)/Sum(Value1)),column(1))   ,lightred(), lightgreen())

Regards,

Guilherme

[mats_kroky]

Hej,

Unfortunately, it doesn't work. No background color is returned 😕

Best,

Mateusz

[mats_kroky · Accepted solution]

Ok - I think I've figure it out. Probably in very unprofessional way but it does the work:

if(sum(value2)/sum(value1)>=0.66,
        lightred(),
        colormix1(Aggr((sum(value2)/sum(value1)),CTRY,BU_NAME)*1.5,lightgreen(),lightred())
)

I used 0.66 to make everything above it red. My results range is typically between 5-30% so I was getting almost everything in green + any value above 100% was not coloured at all. 

Why 0.66? because the factor I used to increase the result is 1,5 and 1/1.5=0.666(6)

Perhaps it will help someone too and still happy to see better solution. 

Best,

Mateusz