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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Advocate II
Advocate II

Dynamically filtering erroneous data



What is the best way to filter out erroneous data points? 


Example data showing a single product's (filtered by a slicer) value over time:



It should look like this (with the erroroneous £185 data point removed):




I can make a calculated column that outputs a high/low/normal flag, but it calculates over the unfiltered table of every product/value.


I don't think I can filter by measures so I don't think I could do something similar with that.


I can't just filter all data that goes above £100 for all products as the (correct) values of different product varies more widely than that.


I can filter the data manually but this isn't as easy for end users.


Here is an example table:



Product     Value
A           £9.30
A           £9.91
A           £9.12
A £185.85 B £31.25 B £31.29
B £0.031 B £31.32
C £0.52
C £0.51
C £0.53
C £0.50


Is there a way to make a calculated column that removes outliers for each product separately, within this table? (or better ways of doing a similar task)


Many thanks.

Super User
Super User



I adapted the solution described in the link below (kudos to the author!). 


1. Create calculated column:


Outlier = 
VAR vMean =
    AVERAGE ( Table1[Value] )
VAR vStdDev =
    STDEV.P ( Table1[Value] )
VAR vResult =
    IF ( Table1[Value] > ( vMean + vStdDev ), 1, 0 )




2. Create measure:


Sum of Value (no Outliers) = 
CALCULATE ( SUM ( Table1[Value] ), Table1[Outlier] = 0 )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.