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

[lennox25]

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... 

[Anonymous · Accepted solution]

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.

[marcelsmaglhaes]

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!