cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yonah
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 CategoryItem descriptionRevenue before returns Returns in Euro Returns rateYearRetourn
BC50100,220210
BD100360,3620191
AE15050,0333333320221
AF200350,17520190
BC300870,2920221
BD250100,0420191
AE50360,7220191
AF10050,05

2019

1

 

In Power BI the matrix looks like this:

 

Bild in Bi.PNG

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.

Thanks in advance.
 

 

1 ACCEPTED SOLUTION
Yonah
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")

View solution in original post

6 REPLIES 6
Yonah
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")
JamesFr06
Solution Supplier
Solution Supplier
JamesFr06
Solution Supplier
Solution Supplier

k, so I think this video could help you.

around 11 minutes it is the same problem as you

JamesFr06
Solution Supplier
Solution Supplier

JamesFr06_0-1652787481064.png

 

JamesFr06
Solution Supplier
Solution Supplier

hy dont you use conditionneal formatting, it will be easier

Yonah
Frequent Visitor

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors