Matrix: Conditional format on KPIs

[Alan_Gould]

Hi, 

Is it possible to create a conditional format on KPIs by row? 

Or is there a workaround? I.e. could I create a new measure? The below KPI matrix table has different targets for different KPIs and I would like to shade to colours if out performing or underperforming. 

Thanks 

Alan 

[m_alireza · Accepted solution]

Hi @Alan_Gould ,

Is this what you are looking for?


You can try something like this measure. You will have to change the columns/tables/fields to match yours as well as add the extra KPIs. You will also need to change the performance range of what constitutes underperforming and overperforming. I added a "Red" and "Green" but you can change them to a hex code.

SWITCH (
    TRUE (),
    MAX ( 'Table'[Performance] ) < 80
        && MAX ( 'Table'[KPI ID] ) = "KPI1", "Red",
    MAX ( 'Table'[Performance] ) < 90
        && MAX ( 'Table'[KPI ID] ) = "KPI2", "Red",
    MAX ( 'Table'[Performance] ) < 90
        && MAX ( 'Table'[KPI ID] ) = "KPI3", "Red",
    MAX ( 'Table'[Performance] ) < 70
        && MAX ( 'Table'[KPI ID] ) = "KPI4", "Red",
    "Green"
)

 
Once you create the measure, add a conditional formatting on your Value Field; Format style should be "Field Value" and field based on should be your measure name e.g.:

[m_alireza · Accepted solution]

Hi @Alan_Gould ,

Is this what you are looking for?


You can try something like this measure. You will have to change the columns/tables/fields to match yours as well as add the extra KPIs. You will also need to change the performance range of what constitutes underperforming and overperforming. I added a "Red" and "Green" but you can change them to a hex code.

SWITCH (
    TRUE (),
    MAX ( 'Table'[Performance] ) < 80
        && MAX ( 'Table'[KPI ID] ) = "KPI1", "Red",
    MAX ( 'Table'[Performance] ) < 90
        && MAX ( 'Table'[KPI ID] ) = "KPI2", "Red",
    MAX ( 'Table'[Performance] ) < 90
        && MAX ( 'Table'[KPI ID] ) = "KPI3", "Red",
    MAX ( 'Table'[Performance] ) < 70
        && MAX ( 'Table'[KPI ID] ) = "KPI4", "Red",
    "Green"
)

 
Once you create the measure, add a conditional formatting on your Value Field; Format style should be "Field Value" and field based on should be your measure name e.g.:

[Alan_Gould]

Hi @m_alireza, 

Quick question, how should I think about writing this if I wanted to have Red if below a certain value, but yellow if between Red and Green thresholds? Would the Switch function still be okay?

The results so far look great! 

Thanks, 
Alan 

[m_alireza · Accepted solution]

Yes it would still work. You just add an extra condition e.g. 

SWITCH (

    TRUE (),

    MAX ( 'Table'[Performance] ) < 20

        && MAX ( 'Table'[KPI ID ) = "KPI1", "Red",

          MAX ( 'Table'[Performance] ) < 60

        && MAX ( 'Table'[KPI ID) = "KPI1", "Yellow",
"Green"

that would color numbers below 60 but above 20 as yellow
anything below 20 would be red
anything above 60 green

[Alan_Gould]

Fantastic!

Works perfectly! Thank you very much 🙂