Forum Discussion

palley92's avatar
palley92
New Member
4 years ago
Solved

How to replace outliers using DAX

I am a complete beginner with DAX, so I apologise in advance if my question has a very easy answer.

Let's say I have a dataset containing two text columns ('Var1' and 'Var2'), and a column with values called 'Values'.

I have built a PivotTable with PowerPivot, where the values are the sum of the 'Values', 'Var1' is in the rows, and 'Var2' is in the columns.

 

What I want to do is to replace those entries that are less than 3 standard deviations below the average across Var1 (but not including Var2) with 0's. 

 

Simply put, I am looking to have a measure that does the following:

calculate the average grouped by Var1, then the standard deviation grouped by var1. Is each value in the pivot table (which is actually grouped by Var1 and Var2) less than the average minus 3x the standard deviation? If so, replace with 0, otherwise keep the value.

 

Many thanks

  • johnt75's avatar
    johnt75
    4 years ago

    I think the below should work

    Replace Outliers =
VAR var1 =
    ALL ( 'Table'[Var1] )
VAR avg =
    AVERAGEX ( var1, CALCULATE ( SUM ( 'Table1'[Value] ) ) )
VAR stdDev =
    STDEVX.P ( var1, CALCULATE ( SUM ( 'Table1'[Value] ) ) )
VAR currentVal =
    CALCULATE ( SUM ( 'Table1'[Value] ) )
RETURN
    IF ( currentVal < avg - ( stdDev * 3 ), 0, currentVal )

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    I think the below should work

    Replace Outliers =
VAR var1 =
    ALL ( 'Table'[Var1] )
VAR avg =
    AVERAGEX ( var1, CALCULATE ( SUM ( 'Table1'[Value] ) ) )
VAR stdDev =
    STDEVX.P ( var1, CALCULATE ( SUM ( 'Table1'[Value] ) ) )
VAR currentVal =
    CALCULATE ( SUM ( 'Table1'[Value] ) )
RETURN
    IF ( currentVal < avg - ( stdDev * 3 ), 0, currentVal )