cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 265 members 2,980 guests
Please welcome our newest community members: