cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amirabedhiafi
Resolver I
Resolver I

Color the max value between 3 measures

I have the following FormatMeasure, I want to color the maximum value between the 3 measures in red : 

FormatMeasure =
VAR _CFR =[CFR]
VAR _CaseFatality = [Case Fatality]
VAR _IFR = [IFR]
RETURN
SWITCH (
TRUE(),
(_CFR > _CaseFatality) && (_CFR >_IFR) , "RED",
(_IFR > _CaseFatality) && (_IFR >_CFR) , "RED",
(_CaseFatality > _IFR) && (_CaseFatality >_CFR) , "RED",
"BLACK"
)
Instead of getting IFR =9.01 in red and the others in black I get them all in black : 
 

Measures.PNG

1 ACCEPTED SOLUTION
Mikelytics
Resolver III
Resolver III

Hi @amirabedhiafi,

 

the following solution should do it also when it might not be the leanest one.

 

You have your 3 measures. -> [measure 1], [measure 2], [measure 3]

picture_1.PNG

Now create one format measure for each measure.

1) indicator measure 1 = IF([measure 1] > [measure 2] && [measure 1] > [measure 3] , 1, 0) 

2) indicator measure 2 = IF([measure 2] > [measure 1] && [measure 2] > [measure 3] , 1, 0) 

3) indicator measure 3 = IF([measure 3] > [measure 1] && [measure 3] > [measure 2] , 1, 0) 

picture_2.PNG

 

Now ... 

create a conditional formatting for each KPI card using the individual indicator measures like the following.

picture_3.jpg

for KPI card 1 with [measure 1] using [indicator measure 1]

picture_4.PNG

for KPI card 2 with [measure 2] using [indicator measure 2]

picture_5.PNG

 

for KPI card 3 with [measure 3] using [indicator measure 3]

picture_6.PNG

 

It worked perfectly also after changing the values for the measures.

picture_7.PNG

picture_8.PNG

Hope this helps.

 

Best regards

Mikelytics

 

----------------------------------------------------------------------------------------------------------------------------------

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.

View solution in original post

6 REPLIES 6
Mikelytics
Resolver III
Resolver III

Hi @amirabedhiafi,

 

the following solution should do it also when it might not be the leanest one.

 

You have your 3 measures. -> [measure 1], [measure 2], [measure 3]

picture_1.PNG

Now create one format measure for each measure.

1) indicator measure 1 = IF([measure 1] > [measure 2] && [measure 1] > [measure 3] , 1, 0) 

2) indicator measure 2 = IF([measure 2] > [measure 1] && [measure 2] > [measure 3] , 1, 0) 

3) indicator measure 3 = IF([measure 3] > [measure 1] && [measure 3] > [measure 2] , 1, 0) 

picture_2.PNG

 

Now ... 

create a conditional formatting for each KPI card using the individual indicator measures like the following.

picture_3.jpg

for KPI card 1 with [measure 1] using [indicator measure 1]

picture_4.PNG

for KPI card 2 with [measure 2] using [indicator measure 2]

picture_5.PNG

 

for KPI card 3 with [measure 3] using [indicator measure 3]

picture_6.PNG

 

It worked perfectly also after changing the values for the measures.

picture_7.PNG

picture_8.PNG

Hope this helps.

 

Best regards

Mikelytics

 

----------------------------------------------------------------------------------------------------------------------------------

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.

View solution in original post

parry2k
Super User III
Super User III

@amirabedhiafi something like this:

 

FormatMeasure =
VAR _CFR =[CFR]
VAR _CaseFatality = CALCULATE ( [Case Fatality], Table1[Column1] = "ABC", Table2[Column2] = "XYZ" ) )
VAR _IFR = [IFR]
RETURN
SWITCH (
TRUE(),
(_CFR > _CaseFatality) && (_CFR >_IFR) , "RED",
(_IFR > _CaseFatality) && (_IFR >_CFR) , "RED",
(_CaseFatality > _IFR) && (_CaseFatality >_CFR) , "RED",
"BLACK"
)

 

what table and column you are using in the filter, apply that here.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





parry2k
Super User III
Super User III

@amirabedhiafi that's your problem, when you doing this color measure, you have to provide the same filter to these measures the same one you are using in KPI visual. The value of the measure is not the same as it is in your KPI visual because of these filters.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





How can I include the filters in the FormatMeasure ?

parry2k
Super User III
Super User III

@amirabedhiafi are these 3 different kpi cards? Is there any filter on these cards?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes I am filtering them by country and date

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors