Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Can we color the background cell in a table
I want to rank the measure in for each(Title col) using colormix gradient.
I am trying to work with this code here
Colormix(aggr(rank(sum([# Apps])),[Row dimension]),,RGB(67,89,56),RGB(x,y,z)
sample data.
title | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
A | 387 | 817 | 974 | 254 | 286 | 437 | 502 | 454 | 704 | 265 | 516 | 499 |
B | 1,349 | 1,501 | 819 | 806 | 1,118 | 815 | 735 | 2,669 | 1,505 | 609 | 675 | 1,302 |
C | 19,716 | 18,177 | 17,082 | 16,170 | 19,124 | 21,197 | 20,417 | 20,963 | 21,113 | 18,219 | 16,838 | 15,830 |
Something like attached.
Rank 1 in each row will be one color,rank 2 in each row will be second color etc.
thanks
Why not try like this (if this is only for 1st three ranks)
If(HRank(Sum([# Apps])) = 1, Color1,
If(HRank(Sum([# Apps])) = 2, Color2,
If(HRank(Sum([# Apps])) = 3, Color3)))
Why not try like this (if this is only for 1st three ranks)
If(HRank(Sum([# Apps])) = 1, Color1,
If(HRank(Sum([# Apps])) = 2, Color2,
If(HRank(Sum([# Apps])) = 3, Color3)))
Thankyou Sunny- I have 12 months so i can define 12 ranks.What is difference between Hrank and Rank?
Can we use gradient coloring here instead of defined solid colors?
HRank is used for pivoted dimensions in a pivot table and Rank is used in Straight table or unpivoted dimensions. Also, read here:
Sunny -thanks again - the code worked.
Super
Sunny when I apply a date filters- i think the code is not working? i selected a date range and i see just one col colored.
If no date filter I see color on every cell.
this is my code
If(HRank(Sum([# Applications])) = 1, RGB(221,217,196),
If(HRank(Sum([# Applications])) = 2, RGB(197,217,241),
If(HRank(Sum([# Applications])) = 3, RGB(242,220,219),
If(HRank(Sum([# Applications])) = 4, RGB(235,241,222),
If(HRank(Sum([# Applications])) = 5, RGB(228,223,236),
If(HRank(Sum([# Applications])) = 6, RGB(218,238,243),
If(HRank(Sum([# Applications])) = 7, RGB(250,191,143),
If(HRank(Sum([# Applications])) = 8, RGB(196,215,155),
If(HRank(Sum([# Applications])) = 9, RGB(146,205,220),
If(HRank(Sum([# Applications])) = 10, RGB(230,184,183),
If(HRank(Sum([# Applications])) = 11, RGB(196,189,151),
If(HRank(Sum([# Applications])) = 12, RGB(191,191,191)))))))))))))
Try this
If(HRank(Sum({1}[# Applications])) = 1, RGB(221,217,196),
If(HRank(Sum({1}[# Applications])) = 2, RGB(197,217,241),
If(HRank(Sum({1}[# Applications])) = 3, RGB(242,220,219),
If(HRank(Sum({1}[# Applications])) = 4, RGB(235,241,222),
If(HRank(Sum({1}[# Applications])) = 5, RGB(228,223,236),
If(HRank(Sum({1}[# Applications])) = 6, RGB(218,238,243),
If(HRank(Sum({1}[# Applications])) = 7, RGB(250,191,143),
If(HRank(Sum({1}[# Applications])) = 8, RGB(196,215,155),
If(HRank(Sum({1}[# Applications])) = 9, RGB(146,205,220),
If(HRank(Sum({1}[# Applications])) = 10, RGB(230,184,183),
If(HRank(Sum({1}[# Applications])) = 11, RGB(196,189,151),
If(HRank(Sum({1}[# Applications])) = 12, RGB(191,191,191)))))))))))))
This will only simplify the nested if's
Pick (
HRank(Sum([# Applications])) , RGB(221,217,196), RGB(197,217,241), ("IN THIS ORDER")
)
Thanks Vishnu- this avoids the confusion on # of braces in nested if stmt.