Présentation des données

Voici à quoi ressemble les données

Using RANKX to find the biggest and smallest numbers

RANKX is a function that can be used in many scenarios when you are calculating the rank of a value based on a category. Imagine that we have a table visual like the below example:

Our first step is to create a measure that can give us the rank of values based on customers. And using RANKX, we can write a measure like below;

123456

Rank of Revenue = RANKX(          ALL(‘By Customer’),          CALCULATE(              SUM(‘By Customer'[Revenue])          ))

“By Customer” is our table name, and Revenue is the column that we want to find out the ranking of it. To learn more about RANKX, read this article.

Now you can add that measure in the table visual, and you will see this output

Using Switch to Choose Color

Now that we know what is the biggest number and what is the smallest number (using the result of RANKX expression), we can set the color based on it. The SWITCH is a function which works like multiple IF THEN ELSE statements. we can write another measure as below;

123456

Background Color = SWITCH(    [Rank of Revenue],    1, »Green »,    25, »Red »,    « White »)

If we want to translate the code above to if-then-else, it means: If the rank of revenue is 1, the color is green, if it is 25, the color is red, and otherwise for any other values it is white. This is the measure that we will be using for coloring the values in the table.

Set Conditional Formatting for Background Color

Now that we have everything ready, we can do the conditional formatting on the table. Select the table visual first, then go to the format tab, and under conditional formatting, select Revenue as the column.

Set the Background color to On, and you will see conditional colors appears immediately. However, this is not the coloring we want. so click on Advanced Controls.

In the advanced control, set the Format By to Field Value, and then select the Background Color measure that we created in the previous step here. then click on OK.

Note that if you are creating your own measures; The measure that you use here, should be producing color values as the output.

After clicking on OK, you will see that there will be only two values color coded, the biggest one as Green, and the smallest one as Red.

Done! You got the very first and the very last number in the ranking now color coded. How simple it is when DAX and Conditional Formatting works with each other! Now, let’s talk about some enhancements.

Font Color: A bit more enhancement

As you can see in the above screenshot, the text value of the revenue is not that much readable because the colors are rather dark. Best would be also creating another measure for Font Color as below;

123456

Font Color = SWITCH(    [Rank of Revenue],    1, »White »,    25, »White »,    « Black »)

Adding that to the table, you can see how it works:

Now, let’s do the conditional formatting this time for Font Color;

In the advanced controls window; Choose the Fixed Value, and then Font Color;

Here is now the final output:

More Enhancements: Color Coding top 3 and bottom 3 values

Well, before you ask for this, I thought better to show you straight away! How if we want to color code not only the top and bottom 1 numbers but also the top/bottom three numbers? Well, that is easily possible, you just need to change your measure expression for the background and font color as below;

12345678910

Background Color Three = SWITCH(    [Rank of Revenue],    1, »Green »,    2, »Green »,    3, »Green »,    23, »Red »,    24, »Red »,    25, »Red »,    « White »)

It is not a good way of writing what we are after. You need to use Switch, but use it for multiple values, here is a better way of doing it:

123456

Background Color Three = SWITCH(    TRUE(),    [Rank of Revenue]<=3, »Green »,    [Rank of Revenue]>=22, »Red »,    « White »)

same for the Font Color;

123456

Font Color Three = SWITCH(    TRUE(),    [Rank of Revenue]<=3, »White »,    [Rank of Revenue]>=22, »White »,    « Black »)

and then if we use these two new measures (Background Color Three, and Font Color Three) for conditional formatting, this is what we get;

What If Parameters for Conditional Formatting: The sky is the limit!

When it comes to combining DAX and visualization, then the sky is the limit of what you can do. Let’s say you don’t know is it the top three that you want to color code, or four, or five. and also you don’t know is it different from the bottom bound of the values. So, as a solution, you can use What If parameters in Power BI to create two parameters; one for the upper bound, and one for the lower bound.

Start by creating a new what if parameter under the modeling tab. name it as Upper Bound, and then minimum as 1, maximum as 10, default as 5, and increment 1 each time. make sure the Add slicer to this page is selected.

Do it one more time for the lower bound with the configuration below;

Now you should have two slicers in your page for each of the what if parameters.

Let’s use these two in our background color and font color measures. This is what the updated background color measure looks like:

12345678910

Background Color Parameter = SWITCH(    TRUE(),    [Rank of Revenue]<=’Upper Bound'[Upper Bound Value], »Green »,    [Rank of Revenue]>=        CALCULATE(            COUNT(‘By Customer'[Customer]),            ALL(‘By Customer’)            )+1-‘Lower Bound'[Lower Bound Value], »Red »,    « White »)

Well, the DAX expression is a bit more complicated than what you expected! Let’s explain a bit of detail here: ‘Upper Bound'[Upper Bound Value] and ‘Lower Bound'[Lower Bound Value] are the values selected in the slicers of what if parameter tables. Because the Lower Bound value is a value between 1 to 10, and we want this value to be deducted from the maximum rank in the revenue column, so I used a calculate function to count all records in the table, and then use that as the source of deduction. I added one to it, to avoid results such as 25-1=24, we want also 25 to be color-coded, which is 25+1-1.

And for the Font color;

12345678910

Font Color Parameter = SWITCH(    TRUE(),    [Rank of Revenue]<=’Upper Bound'[Upper Bound Value], »White »,    [Rank of Revenue]>=        CALCULATE(            COUNT(‘By Customer'[Customer]),            ALL(‘By Customer’)            )+1-‘Lower Bound'[Lower Bound Value], »White »,    « Black »)

After conditional formatting this is the result:

It works even for other visuals

The solution you learned in this article, is not just for table visual. you can use it in a bar chart, column chart, and some other visuals. Obviously for these, because the background color white would be a bit invisible, I changed the measure as below;

12345678910

Background Color Parameter Chart = SWITCH(    TRUE(),    [Rank of Revenue]<=’Upper Bound'[Upper Bound Value], »Green »,    [Rank of Revenue]>=        CALCULATE(            COUNT(‘By Customer'[Customer]),            ALL(‘By Customer’)            )+1-‘Lower Bound'[Lower Bound Value], »Red »,    « Orange »)

The above measure replaced the White color with Orange.

And here is the output now:

All in one as a wrap up looks like this:

Summary