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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
emarc1
Advocate II
Advocate II

Dynamically filtering erroneous data

Hello.

 

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.

1 REPLY 1
DataInsights
Super User
Super User

@emarc1,

 

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

 

https://bielite.com/blog/scale-down-outliers-power-bi/ 

 

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 )
RETURN
    vResult

 

DataInsights_2-1665262898133.png

 

2. Create measure:

 

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

 

DataInsights_1-1665262617788.png

 





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

Proud to be a Super User!




Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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