cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

Color= if(Messure[Returns rate]-DIVIDE(CALCULATE(Messure[ Returns in Euro ]),Messure[Revenue before returns,0)>0,"green","red") but this does not work.

1 ACCEPTED SOLUTION
Frequent Visitor

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 Measure

Returns 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 6
Frequent Visitor

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 Measure

Returns 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")
Solution Supplier
Solution Supplier

around 11 minutes it is the same problem as you

Solution Supplier

Solution Supplier

hy dont you use conditionneal formatting, it will be easier

Frequent Visitor

Since it then refers to the entire matrix and not to the column.

Announcements