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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Alan_Gould
Frequent Visitor

Matrix: Conditional format on KPIs

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. 

 

Alan_Gould_0-1674793687372.png

 

Thanks 

Alan 

 

2 ACCEPTED SOLUTIONS
m_alireza
Solution Specialist
Solution Specialist

Hi @Alan_Gould ,

Is this what you are looking for?
Screenshot 2023-01-27 132536.png

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.:Screenshot 2023-01-27 132445.png

View solution in original post

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

View solution in original post

4 REPLIES 4
m_alireza
Solution Specialist
Solution Specialist

Hi @Alan_Gould ,

Is this what you are looking for?
Screenshot 2023-01-27 132536.png

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.:Screenshot 2023-01-27 132445.png

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! 

 

Alan_Gould_0-1675142500039.png

 

 

Thanks, 
Alan 

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

Fantastic!

 

Works perfectly! Thank you very much 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.