Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yonah
Helper II
Helper II

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
Helper II
Helper II

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
Helper II
Helper II

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")
Anonymous
Not applicable
Anonymous
Not applicable

k, so I think this video could help you.

around 11 minutes it is the same problem as you

Anonymous
Not applicable

JamesFr06_0-1652787481064.png

 

Anonymous
Not applicable

hy dont you use conditionneal formatting, it will be easier

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.