Forum Discussion
Power BI conditional formatting per column in Matrix
Hello,
I have a matrix in PowerBI with rows item category, item description, the column Year and the values Sales before Returns, Returns in Euro and Returns Quotas. The data looks like this:
| Article Category | Item description | Revenue before returns | Returns in Euro | Returns rate | Year | Retourn |
| B | C | 50 | 10 | 0,2 | 2021 | 0 |
| B | D | 100 | 36 | 0,36 | 2019 | 1 |
| A | E | 150 | 5 | 0,03333333 | 2022 | 1 |
| A | F | 200 | 35 | 0,175 | 2019 | 0 |
| B | C | 300 | 87 | 0,29 | 2022 | 1 |
| B | D | 250 | 10 | 0,04 | 2019 | 1 |
| A | E | 50 | 36 | 0,72 | 2019 | 1 |
| A | F | 100 | 5 | 0,05 | 2019 | 1 |
In Power BI the matrix looks like this:
My client wants the Returns rate column to be formatted based on the total in that column.
If above the total value of the column, the formatting should be red, below green.
It try it using
Thanks in advance.
I found a Soulotion.
First, I calculated the Amount of Return.To get the Amount of Return per Year/per Column, I used this Measure:
Returns per year = DIVIDE(CALCULATE(Messure[Returns in Euro],ALLSELECTED(T1[ItemCategoryReference])),CALCULATE(Messure[Sales before Returns],ALLSELECTED(T1[ItemCategoryReference]),0)*100
To get the Return Ratio. I used this MeasureReturns ratio = DIVIDE(Messure[Returns in Euro],Messure[Sales before returns],0)*100
For the Color-Condion
Color 2 = if(Messure[returns rate]>Messure[returns per year], "red", "green")
I then used Color 2 as Field value in conditional formatting.Color2 = if(Messure[returns rate]>Messure[returns per year], "red", "green")
6 Replies
I found a Soulotion.
First, I calculated the Amount of Return.To get the Amount of Return per Year/per Column, I used this Measure:
Returns per year = DIVIDE(CALCULATE(Messure[Returns in Euro],ALLSELECTED(T1[ItemCategoryReference])),CALCULATE(Messure[Sales before Returns],ALLSELECTED(T1[ItemCategoryReference]),0)*100
To get the Return Ratio. I used this MeasureReturns ratio = DIVIDE(Messure[Returns in Euro],Messure[Sales before returns],0)*100
For the Color-Condion
Color 2 = if(Messure[returns rate]>Messure[returns per year], "red", "green")
I then used Color 2 as Field value in conditional formatting.Color2 = if(Messure[returns rate]>Messure[returns per year], "red", "green")Since it then refers to the entire matrix and not to the column.
