Forum Discussion

emarc1's avatar
emarc1
Advocate II
9 years ago

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

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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

     

     

    2. Create measure:

     

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