Color of top valued dimension in the Table chart

[AlexWest]

Hi everyone!

I have a table chart:

Client	sum(Income)
1	20
2	30
3	50

...

As you can see, 20, 30, 50 are a sum(Income), and when I filter any value, clients are changing.

And I need to color Client's name which at the time has the biggest Income.

So may be someone can hint me what expression should I use in a Client's Background color expression to color the most Incoming?

Thanks in advance!

[Happy_Mask_Salesman · Accepted solution]

Hi Alex.

Easy one.

Rank() function allows you to rank an expression in a numerical representation, for each row by the dimensions of the chart.

Just check how it would behave as a new expression in your table chart.

Client	sum(Income)	Rank(sum(Income))
1	20	3
2	30	2
3	50	1

Based on this, you many options.

1. Indicate manually colours for each position, using combination of Pick, Match, and Rank function.

Example: I'm going to colour the highest on the rank, red, the second, blue, and the third, green.

Pick(Match(Rank(sum(Income)), '1','2','3'), Red(), Blue(), Green())

Obviously instead of Red(), Blue(), Green() functions, you can use the function Rgb() to indicate exactly which colours do you want to use.

2. Range of colours based on its rank. Colormix1() function.

Colormix1() function, assigns a colour mix between two colours. The first colour should have associated the lowest (3) number and the second colour, the highest value possible (1)

If you wish to assign a colour between green and red, its easy.

Colormix1( Rank(sum(Income))/NoOfRows(), LightGreen(), LightRed())

The first parameter must be a %, or a value between 0 and 1. If there are three rows, each number between 1 and 3, get the total of rows from the table chart with NoOfRows() and the value from Rank().

This would result in a interval of colors between a light green and a light red, with the middle value being on a brown zone.

3. Only a colour for the highest value.

If you want to highlight highest income customer, just use:

if(rank(sum(income))=1, green()).

You don't need the Rank() formula in your chart as a new expression, I'm showing it in the screenshots for educational purpose.

Feel free to ask any doubts.

Regards.

[AlexWest]

Thanks a lot! Your solution with Rank() is what I needed