Color the max value between 3 measures

[amirabedhiafi]

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 : 

[Mikelytics · Accepted solution]

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]

 

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) 

Now ... 

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

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

 

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

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

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

 

 

Hope this helps.

Best regards

Mikelytics

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

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

Appreciate your Kudos.

[parry2k]

@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.⚡

[parry2k]

@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.

[amirabedhiafi]

How can I include the filters in the FormatMeasure ?

[parry2k]

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

[amirabedhiafi]

Yes I am filtering them by country and date