Conditional formatting based on bechmarks that differ by columns

[Anonymous]

Hello,

I have a matrix with facilities listed in the rows and tests listed in the columns. The values in the middle of the table are a measure calculated as the number of times a facility exceeds the test divided by the total number of facilities taking the test.

Test A, B, C

Facility 1 - 33%, 0%, 77.27%

Facility 2 - 66.67%, 77.78%, 100%

Facility 3 - 56.67%, 0%, 33.33%

Test A has a benchmark of 50%, Test B has a benchmark of 70%, Test C has a benchmark of 30%. 

I would like to apply conditional formatting to the cell values based on the bench of each column. I believe this can be achieved by adding conditional formatting by field value the formatting doesn't seem to apply in all situations.

Color = SWITCH(TRUE(),'APP'[Test]=A&&[AnswerStatus%]>.5, "#008000", 
'APP'[Test]=B&&[AnswerStatus%]>.70, "#008000", 'APP'[Test]=C&&[AnswerStatus%]>.30, "#008000")

My ideal soltuion is:

Test A, B, C

Facility 1 - 33%, 0%, 77.27%

Facility 2 - 66.67%, 77.78%, 100%

Facility 3 - 56.67%, 0%, 33.33%

A bonus would be to even add an additional column that would be a count of the number of greens in a row.

Test A, B, C, COUNT_GREEN

Facility 1 - 33%, 0%, 77.27%, 1

Facility 2 - 66.67%, 77.78%, 100%, 3

Facility 3 - 56.67%, 0%, 33.33%, 2

Thanks so much in advance for the DAX genius 🙂

Ashley

[Anonymous]

Hi @Anonymous 

Are you missing a 'else' condition?

i.e. 

Color = SWITCH(TRUE(),'APP'[Test]=A&&[AnswerStatus%]>.5, "#008000", 
'APP'[Test]=B&&[AnswerStatus%]>.70, "#008000", 'APP'[Test]=C&&[AnswerStatus%]>.30, "#008000", "#ff0000")

Another way is, to create 3 calculated columns for each test result.

i.e.

Column_A_Result = IF(AnswerStatus>0.5,1,2)

Then format the column according to this number/column (1=green,2-red). And you can sum up the 1s (column_sum = SUM(Column_A_Result,Column_B_Result ,Column_C_Result ))

Good luck!
A

[Anonymous]

Thanks so much for the assist @Anonymous!

The else statement isn't needed with the switch.  Instead, I added a >= and a < clause - but no dice. 

Exploring the column_result option. If I want to format the column according to this number/column (1=green,2-red), where in PowerBI can I apply the format to each column individually?

[Anonymous · Accepted solution]

Hi @Anonymous 

You need to go to FORMAT section (1).

There choose CONDITIONAL FORMATING (2).

Choose the field to format. Another window will pop up.

Choose RULES (3), and BASED ON FIELD - Will be the new calculated field (4).

In the example, I format NAME according to COUNT of IDs.