In this writing, I want to share how I try to conditionally format each line (not by all numbers resulted by the measure) in a matrix visualization in Power BI. This may not be the most solid solution, but so far, I could not find any other solutions that give easy understanding than this. Please share if you know other ways to solve this, and I love to see and learn how other solutions work that show it in a more intuitive way.
In general, when applying the conditional format in a matrix visualization, it is applied by using the result of the measure, like the below figure. The measure is,
Qty measure: = SUM( Data[Qty] )
As shown in the sample above, 87 is the highest number in the matrix visualization, and the background is colored green. Numbers that are in the range of middle are colored in yellow, and numbers that are in the lower range are colored in red. If the result is blank, the background is not formatted. The setting of the background color is configured like below.
Problem:
However, quite often, end users request to see color format by country. In this case, NL should contain green, yellow, and red backgrounds (because there are three categories). Furthermore, IT should contain green and red backgrounds (because there are two categories).
Solution:
In order to achieve this, I need to have new numbers that represent the highest number in each country as rank 1, and the lowest number in each country as rank 5. The blank will remain unformatted. As you may imagine from the description, I will try to use the RANKX DAX function to achieve this.
The sample file's data model looks like the below (the sample pbix file is attached at the end of this blog post).
The Data table in the sample looks like below.
I create a matrix visualization. I insert Country[Country] as Rows, Category[Category] as Columns, and two measures as Values.
Qty measure: = SUM( Data[Qty] )
Qty rank index country level: = VAR _categorycount = COUNTROWS ( FILTER ( ALL ( Category[Category] ), NOT ISBLANK ( [Qty measure:] ) ) ) VAR _ranking = IF ( NOT ISBLANK ( [Qty measure:] ), RANKX ( ALL ( Category[Category] ), [Qty measure:],, DESC ) ) RETURN IF ( _ranking = 1, 1 / COUNTROWS ( ALL ( Category[Category] ) ), DIVIDE ( _ranking, _categorycount ) ) //by doing this, rank 1 always shows 0.2 and rank-last always shows 1 in each country.
And then, configure the setting of the background color of [Qty measure:] like below.
From the above matrix visualization, remove [Qty rank index country level:] measure. And then the below matrix visualization is shown.
As shown above, the highest number in each country is colored in green, and the lowest number in each country is colored in red.
I hope there are many other ways to achieve this and I love to learn. Please share your ways to achieve this.
I hope this helps to have fun configuring conditional formatting options in many different ways.
Comments