Forum Discussion

lennox25's avatar
lennox25
Post Patron
1 year ago
Solved

Conditional Formatting- 2 Std Deviation below Average and 2 Std Deviation above Average

Hi, 

Each Column is derived from a measure. Here is my measure

Apples Percentage =
VAR TotalSales = SUM('Table 2'[Total Sales])
VAR AppleSales =
    CALCULATE(
        SUM('Table 1'[Amount]),
        'Table 1'[Category] = "Apples"
    )  
RETURN
    DIVIDE(AppleSales, TotalSales, 0)
 

I have been asked to add conditional formatting to this table in Power BI.  Can anyone help me with this please? Please see example of my sample data below from Excel.

 

 

I attach my sample data - https://docs.google.com/spreadsheets/d/1IyTWtuzcQtblXIbr7Kyz8rGfK9DG0RW3/edit?usp=sharing&ouid=10762... 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi lennox25 ,

    Thanks for marcelsmaglhaes's reply!
    And lennox25 , you can also use DAX to achieve this.
    Here is my sample data:

    I see that you seem to be involved in calculating 2 Std Deviation and Average, but I don't know exactly which values you are calculating for these two values, so I'll just substitute two random DAXs on my end, please replace them with the ones that you are actually using.

    AverageAmount = 
CALCULATE(
    AVERAGE('Table 1'[Amount]),
    ALLEXCEPT('Table 1', 'Table 1'[Score No], 'Table 1'[Category])
)
    STDEVAmount = 
CALCULATE(
    STDEV.P('Table 1'[Amount]),
    ALLEXCEPT('Table 1', 'Table 1'[Score No], 'Table 1'[Category])
)

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • marcelsmaglhaes's avatar
    marcelsmaglhaes
    Super User
    1 year ago

    Hey lennox25 ,

    Since you have the meausre for the each percentage, add the calculated measures to your table in Power BI then:

    • Apply Conditional Formatting:

      • Select the table visual in Power BI.
      • Click the dropdown for the measure you want to apply conditional formatting to (e.g., Apples Percentage).
      • Choose Conditional Formatting > Background Color or Font Color.

    • Configure Conditional Formatting Rules:

      • In the Conditional Formatting window:
        • Set the Format by option to Rules.
        • Create rules similar to your Excel format:
          • Cell Value = 0 → Apply a specific color.
          • Below Average → Choose a color (similar to your red highlight for below average).
          • Above Average → Choose another color.

    Apply the same conditional formatting logic to other columns (Pears Percentage, Oranges Percentage, etc.) using their respective measures.

    Let me know if that helps!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi lennox25 ,

    Thanks for marcelsmaglhaes's reply!
    And lennox25 , you can also use DAX to achieve this.
    Here is my sample data:

    I see that you seem to be involved in calculating 2 Std Deviation and Average, but I don't know exactly which values you are calculating for these two values, so I'll just substitute two random DAXs on my end, please replace them with the ones that you are actually using.

    AverageAmount = 
CALCULATE(
    AVERAGE('Table 1'[Amount]),
    ALLEXCEPT('Table 1', 'Table 1'[Score No], 'Table 1'[Category])
)
    STDEVAmount = 
CALCULATE(
    STDEV.P('Table 1'[Amount]),
    ALLEXCEPT('Table 1', 'Table 1'[Score No], 'Table 1'[Category])
)

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.