cancel
Showing results for
Did you mean:
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 :

1 ACCEPTED SOLUTION
Resolver III

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

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

6 REPLIES 6
Resolver III

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

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

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.

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

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.

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

Resolver I

How can I include the filters in the FormatMeasure ?

Super User III

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

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

Resolver I

Yes I am filtering them by country and date

Announcements