give number a background color as per amount

[ninawang99]

Hi all,

I am building a pivot table as per below,

I tried to rank all of the numbers in the pivot table and then give them background colors as per ranking results.

My syntax like:

colormix2( (hrank( [Total Amount]) / NoOfColumns(total) )*2-1, ARGB(255, 220, 0, 0), ARGB(255, 0, 200, 0), ARGB(100,255,255, 0) )

but seems the results are not correct when compared with below sample excel file (which used conditional formatting - color scale function in Excel)

My pivot table has 3 dimensions:

row - Year(date)

column - weekday(date) and Values

Values come from measures

😧 count({<shift = {'D'}>} Order_number)

N: count({<shift = {'N'}>} Order_number)

E: count({<shift = {'E'}>} Order_number)

Is that possible to applied the colors into that pivot tables?

Thanks in advance

[vinieme12]

try below CORRECTION

Colormix1( sum([Amount])/ Max(TOTAL Aggr( sum([Amount]) , Dimension)) , ARGB(255, 220, 0, 0) , ARGB(255, 0, 200, 0))

[ninawang99]

Hi Vinieme,

Thanks for your reply, but seems not working. It might due to the dimensions. In my pivot table, one of the column dimension is weekday(date), when using aggr(), seems the values cannot be grouped by weekday(DT).

Do you have any idea to fix that? 

Thanks

[vinieme12]

Aggr() only works with actual dimensions,  not calculated ones

so i suggest you create a weekday field in your datamodel