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 valu...
  • 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 )
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 )