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.
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
2. Create measure:
Sum of Value (no Outliers) =
CALCULATE ( SUM ( Table1[Value] ), Table1[Outlier] = 0 )
