find out the outlier in matrix visual and highlight with color

[sahgir123]

Hello everyone,

I'm seeking assistance with identifying outliers in a matrix visual using Power BI. In my matrix visual, I've structured the rows with the "Sales Month" column and the columns with the "Month Change" column. The values are populated using a measure called "Sales Ratio," which is calculated as the cumulative count of claims divided by sales for each month. For instance, if there were 30 sales in January 2023 and 15 claims, the sales ratio would be 15/30 = 0.5.

Now, I'm looking to detect outliers using the IQR (Interquartile Range) method at the column level within the matrix visual. My goal is to assign red and green colors based on whether a value is identified as an outlier or not.

Could someone please guide me on how to achieve this effectively in Power BI?

Thank you in advance for your help!
Also help me to understand how to optimized the power bi report, I have two main table each table contain more than 70lakh  rows, due to the cummulative measure and report is too much slow how i can boost the performance. i am using Sharepoint folder as data source. i have combine multiple excel file. any suggestion please.

How IQR work you can take an example like :

A teacher wants to examine students’ test scores. Their scores are: 74, 88, 78, 90, 94, 90, 84, 90, 98, and 80.

Five number summary: 74, 80, 89, 90, 98.

𝐼𝑄𝑅=90−80=10

The interquartile range is 10.

1.5𝐼𝑄𝑅=1.5(10)=15

1.5 times the interquartile range is 15. Our fences will be 15 points below Q1 and 15 points above Q3.

Lower fence: 80−15=65
Upper fence: 90+15=105

Any scores that are less than 65 or greater than 105 are outliers. In this case, there are no outliers.

	0	1	2	3	4	5	6
Jan-23	0.5	0.6	0.8	0.9	1.0	6.0	6.0
Feb-23	0.2	0.5	0.8	0.9	1.0	5.0	6.0
Mar-23	2.0	2.3	2.7	2.9	3.0	9.0	7.0
Apr-23	1.0	2.0	2.4	3.0	3.0	8.0	6.0
May-23	0.0	5.0	6.0	7.0	7.2	8.0	9.0

[Anonymous · Accepted solution]

Hi @sahgir123 ,

1. conditional format

Please try this measure:

Measure = 
VAR __Q1 = PERCENTILE.INC('Table'[Scores], 0.25)
VAR __Q3 = PERCENTILE.INC('Table'[Scores], 0.75)
VAR __IQR = __Q3 - __Q1
VAR __150IQR = 1.5 * __IQR
VAR __Lower_fence = __Q1 - __150IQR
VAR __Upper_fence = __Q3 + __150IQR
VAR __result = IF([Total Scores]>=__Lower_fence && [Total Scores]<=__Upper_fence,"Green","Red")
RETURN
__result

2. Here is an optimization guide:

Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group