Forum Discussion

ehahn's avatar
ehahn
Frequent Visitor
9 years ago
Solved

AVERAGEX -- EXCLUDING OUTLIERS?

I have a custom measure [% Change] which is calculating the month-over-month percent change of value over time. This measure is included in a matrix to display [% Change] for multiple accounts.

 

I have another custom measure [Average Return] which is calculating the average of [% Change] for each date period as defined by the rows of the matrix. 

 

everything is working, HOWEVER... I now want to EXCLUDE outliers from the [Average Return] calculation. 

 

currently Average Return = AVERAGEX(values(VeoBalHistory[Date]),[% Change])

 

any ideas on how to calculate the above but exclude outliers (for example the -47.62% displayed below)?

Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    ehahn

    I would recommend a measure like this:

     

    Average Return Excluding Outliers =
// Use a method of your choosing to define thresholds for outliers
    // This example uses:
    //   Lower Quartile - 1.5 * Interquartile range &
    //   Upper Quartile + 1.5 * Interquartile range 
VAR LowerQuartile =
    PERCENTILEX.INC ( VALUES ( VeoBalHistory[Date] ), [% Change], .25 )
VAR UpperQuartile =
    PERCENTILEX.INC ( VALUES ( VeoBalHistory[Date] ), [% Change], .75 )
VAR InterQuartileRange = UpperQuartile - LowerQuartile
// Anything < OutlierThresholdLower is an outlier
VAR OutlierThresholdLower = LowerQuartile
    - InterQuartileRange * 1.5
// Anything > than OutlierThresholdUpper is an outlier
VAR OutlierThresholdUpper = UpperQuartile
    + InterQuartileRange * 1.5
    // Calculate average over [% Change] only for dates when [% Change] is not an outlier
    // For dates where [% Change] is an outlier, the IF function returns BLANK()
    // which is not included in the average calculated by AVERAGEX
RETURN
    AVERAGEX (
        VALUES ( VeoBalHistory[Date] ),
        VAR ChangePct = [% Change]
        RETURN
            IF (
                AND ( ChangePct >= OutlierThresholdLower, ChangePct <= OutlierThresholdUpper ),
                ChangePct
            )
    )

     

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Can you wrap your AVERAGEX in an CALCULATE and filter out outliers that way?

    • ehahn's avatar
      ehahn
      Frequent Visitor
      9 years ago

      new to DAX, so not sure how to do that.

    • ehahn's avatar
      ehahn
      Frequent Visitor
      9 years ago

      and keep in mind, my goal is to exclude the outlier values from the average calculation.

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        9 years ago

        ehahn

        I would recommend a measure like this:

         

        Average Return Excluding Outliers =
// Use a method of your choosing to define thresholds for outliers
        // This example uses:
        //   Lower Quartile - 1.5 * Interquartile range &
        //   Upper Quartile + 1.5 * Interquartile range 
VAR LowerQuartile =
    PERCENTILEX.INC ( VALUES ( VeoBalHistory[Date] ), [% Change], .25 )
VAR UpperQuartile =
    PERCENTILEX.INC ( VALUES ( VeoBalHistory[Date] ), [% Change], .75 )
VAR InterQuartileRange = UpperQuartile - LowerQuartile
// Anything < OutlierThresholdLower is an outlier
VAR OutlierThresholdLower = LowerQuartile
    - InterQuartileRange * 1.5
// Anything > than OutlierThresholdUpper is an outlier
VAR OutlierThresholdUpper = UpperQuartile
    + InterQuartileRange * 1.5
        // Calculate average over [% Change] only for dates when [% Change] is not an outlier
        // For dates where [% Change] is an outlier, the IF function returns BLANK()
        // which is not included in the average calculated by AVERAGEX
RETURN
    AVERAGEX (
        VALUES ( VeoBalHistory[Date] ),
        VAR ChangePct = [% Change]
        RETURN
            IF (
                AND ( ChangePct >= OutlierThresholdLower, ChangePct <= OutlierThresholdUpper ),
                ChangePct
            )
    )