Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        Anonymous
        Not applicable

        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.