Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
lennox25
Post Patron
Post Patron

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

Hi, 

Each Column is derived from a measure. 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.

 

lennox25_0-1731408355627.png

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

2 ACCEPTED SOLUTIONS
saud968
Super User
Super User

Here are the steps on how to add conditional formatting to your table in Power BI using the "2 Std Deviation below Average" and "2 Std Deviation above Average" criteria:

1. Create a Measure for Standard Deviation:

In your Power BI model, create a measure to calculate the standard deviation for each fruit column. You can use the STDEVX.P function for this. Here's an example for the "Apples" column:
Standard Deviation = STDEVX.P(ALLSELECTED('Table'[Store No]), 'Table'[Apples])
Create similar measures for the other fruit columns.
2. Create Measures for Upper and Lower Limits:

Create measures to calculate the upper and lower limits based on 2 standard deviations above and below the average for each fruit column. Here's an example for the "Apples" column:
Upper Limit = AVERAGE('Table'[Apples]) + 2 * [Standard Deviation]
Lower Limit = AVERAGE('Table'[Apples]) - 2 * [Standard Deviation]
Create similar measures for the other fruit columns.
3. Apply Conditional Formatting:

Right-click on the table visual and select "Format."
Under the "Table" section, find the "Values" section.
Click the "fx" icon next to the "Background color" property.
In the formula bar, enter the following formula for the "Apples" column:
IF(
'Table'[Apples] < [Lower Limit],
RGB(255, 0, 0), // Red for below lower limit
IF(
'Table'[Apples] > [Upper Limit],
RGB(0, 255, 0), // Green for above upper limit
BLANK() // No color for values within limits
)
)
Adjust the RGB values to your desired colors for the below and above limits.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!


View solution in original post

v-zhouwen-msft
Community Support
Community Support

Hi saud968 ,thanks for the quick reply, I'll add more.

Hi @lennox25 ,

Use the following DAX expression to create a measure

_apples = 
VAR _apples = MAX('Table'[Apples])
VAR _applesAvg = CALCULATE(AVERAGE('Table'[Apples]),ALL('Table'))
VAR _stdApples = CALCULATE(STDEV.P('Table'[Apples]),ALL('Table'))
VAR _resultAboveApples = _applesAvg + 2 *_stdApples
VAR _resultBelowApples = _applesAvg - 2 *_stdApples
RETURN 
SWITCH(TRUE(),
 _apples = 0  ,"Purple",
_apples > _resultAboveApples || _apples < _resultBelowApples , "Red" 
)

vzhouwenmsft_1-1731488613932.png

vzhouwenmsft_2-1731488637831.png

vzhouwenmsft_0-1731488325640.png

 

Please refer to the attachment for details.

 

Best Regards,
Wenbin Zhou

View solution in original post

2 REPLIES 2
v-zhouwen-msft
Community Support
Community Support

Hi saud968 ,thanks for the quick reply, I'll add more.

Hi @lennox25 ,

Use the following DAX expression to create a measure

_apples = 
VAR _apples = MAX('Table'[Apples])
VAR _applesAvg = CALCULATE(AVERAGE('Table'[Apples]),ALL('Table'))
VAR _stdApples = CALCULATE(STDEV.P('Table'[Apples]),ALL('Table'))
VAR _resultAboveApples = _applesAvg + 2 *_stdApples
VAR _resultBelowApples = _applesAvg - 2 *_stdApples
RETURN 
SWITCH(TRUE(),
 _apples = 0  ,"Purple",
_apples > _resultAboveApples || _apples < _resultBelowApples , "Red" 
)

vzhouwenmsft_1-1731488613932.png

vzhouwenmsft_2-1731488637831.png

vzhouwenmsft_0-1731488325640.png

 

Please refer to the attachment for details.

 

Best Regards,
Wenbin Zhou

saud968
Super User
Super User

Here are the steps on how to add conditional formatting to your table in Power BI using the "2 Std Deviation below Average" and "2 Std Deviation above Average" criteria:

1. Create a Measure for Standard Deviation:

In your Power BI model, create a measure to calculate the standard deviation for each fruit column. You can use the STDEVX.P function for this. Here's an example for the "Apples" column:
Standard Deviation = STDEVX.P(ALLSELECTED('Table'[Store No]), 'Table'[Apples])
Create similar measures for the other fruit columns.
2. Create Measures for Upper and Lower Limits:

Create measures to calculate the upper and lower limits based on 2 standard deviations above and below the average for each fruit column. Here's an example for the "Apples" column:
Upper Limit = AVERAGE('Table'[Apples]) + 2 * [Standard Deviation]
Lower Limit = AVERAGE('Table'[Apples]) - 2 * [Standard Deviation]
Create similar measures for the other fruit columns.
3. Apply Conditional Formatting:

Right-click on the table visual and select "Format."
Under the "Table" section, find the "Values" section.
Click the "fx" icon next to the "Background color" property.
In the formula bar, enter the following formula for the "Apples" column:
IF(
'Table'[Apples] < [Lower Limit],
RGB(255, 0, 0), // Red for below lower limit
IF(
'Table'[Apples] > [Upper Limit],
RGB(0, 255, 0), // Green for above upper limit
BLANK() // No color for values within limits
)
)
Adjust the RGB values to your desired colors for the below and above limits.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!


Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!