cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ash1234 Frequent Visitor
Frequent Visitor

Conditional formatting based on bechmarks that differ by columns

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

1 ACCEPTED SOLUTION

Accepted Solutions
AClerk New Contributor
New Contributor

Re: Conditional formatting based on bechmarks that differ by columns

Hi @ash1234 

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.

cond formating.png

 

View solution in original post

3 REPLIES 3
AClerk New Contributor
New Contributor

Re: Conditional formatting based on bechmarks that differ by columns

Hi @ash1234 

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

ash1234 Frequent Visitor
Frequent Visitor

Re: Conditional formatting based on bechmarks that differ by columns

Thanks so much for the assist @AClerk!

 

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?

 

AClerk New Contributor
New Contributor

Re: Conditional formatting based on bechmarks that differ by columns

Hi @ash1234 

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.

cond formating.png

 

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors