Multiple Condition Conditional Formats Matrix Table

[alpeytongreene]

I am working on benchmarking data visualizations. 

I need to format a row total in a matrix table to show a yellow or red value based on the percentage and which size facility it is. I have the categories for the locations and their names  in the table as well. 

Facility Sizes - Values

>200 Beds-  17.1%- 21.09% (Yellow), 21.1% and greater (Red)

200-349 Beds- 19.8% - 29.79%(Yellow) 29.8% and greater (Red)

350- 500 Beds- 16.9%- 26.89% (Yellow), 26.9% and greater (Red)

>500 Beds- 15.9%- 25.89 (Yellow), 25.9% and greater (Red)

Anything less than those I would need as white. 

Examples

Hospital A - >200 Beds has a turnover of 17% they would be white, but when they have a few more leave it becomes 20.15% so at that time it would become yellow. 

Example of Location and Percentage

BUT 

If location B has a bed size of 350-500 and has 16% it would be white, but when they have several more terms they would go to 18% and be yellow as well. 

[parry2k · Accepted solution]

@alpeytongreene try this then

Color Measure = 
VAR __bedsCount = MAX ( Table[Bed Count Column] )
VAR __turnover = [Percentage Measure]
RETURN
SWITCH ( TRUE(),
   __bedsCount "> 500 Beds", IF ( __turnover > 0.259, "Red", IF ( __turnover > 0.159, "Yellow" ) ),
   __bedsCount "350-500 Beds", IF ( __turnover > 0.269, "Red", IF ( __turnover > 0.169, "Yellow" ) ),
   __bedsCount "<200 Beds", IF ( __turnover > 0.298, "Red", IF ( __turnover > 0.198, "Yellow" ) ),
    IF ( __turnover > 0.211, "Red", IF ( __turnover > 0.171, "Yellow" ) )
)

make sure value exactly matches with the values in your data.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

[alpeytongreene]

Hello, I am finally getting to circle back- I am receiving an error message on the hospital size information. I have replaces the quotation marks since BI does not like them. It says I have an incorrect syntax for the dax. 

[parry2k]

@alpeytongreene I assume it is a number column, change the measure as below:

Color Measure = 
VAR __bedsCount = SUM ( Table[Bed Count Column] )
VAR __turnover = [Percentage Measure]
RETURN
SWITCH ( TRUE(),
   __bedsCount > 500, IF ( __turnover > 0.259, "Red", IF ( __turnover > 0.159, "Yellow" ) ),
   __bedsCount > 349, IF ( __turnover > 0.269, "Red", IF ( __turnover > 0.169, "Yellow" ) ),
   __bedsCount > 199, IF ( __turnover > 0.298, "Red", IF ( __turnover > 0.198, "Yellow" ) ),
    IF ( __turnover > 0.211, "Red", IF ( __turnover > 0.171, "Yellow" ) )
)

✨ Follow us on LinkedIn and subscribe to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

[alpeytongreene]

It is just a category, not a number. So the hospital/bed size is a category for a turnover. So there are the

>200 Beds 

and 

350-500 Beds

[parry2k]

what do you refer to hospital size, bed count?

[alpeytongreene]

Yes, the bed count would be the same as hospital size. Apologies for that. 

[parry2k]

@alpeytongreene assuming these are measures, bed count and % and use following measure to get the color and then use it in the conditional formatting, tweak the formula as per your need

Color Measure = 
VAR __bedsCount = [Bed Count Measure]
VAR __turnover = [Percentage Measure]
RETURN
SWITCH ( TRUE(),
   __bedsCount > 500, IF ( __turnover > 0.259, "Red", IF ( __turnover > 0.159, "Yellow" ) ),
   __bedsCount > 349, IF ( __turnover > 0.269, "Red", IF ( __turnover > 0.169, "Yellow" ) ),
   __bedsCount > 199, IF ( __turnover > 0.298, "Red", IF ( __turnover > 0.198, "Yellow" ) ),
    IF ( __turnover > 0.211, "Red", IF ( __turnover > 0.171, "Yellow" ) )
)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.⚡

[alpeytongreene]

The Hospital size is not a measure, it is a conditional column category.