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

[lennox25]

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.

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

[saud968 · Accepted solution]

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!

[v-zhouwen-msft · Accepted solution]

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" 
)

Please refer to the attachment for details.

Best Regards,
Wenbin Zhou

[saud968 · Accepted solution]

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!