Remove outliers- greater than 2 standard deviation from mean

[Clint]

Hello,

I have a dataset that includes service desk ticket info including the total time to close a ticket.  I have constructed a table (see image) that averages this across priority and by customer group.  What I've been asked to do is to filter out outlier values (highlighted) so that the averages aren't as skewed as they would be otherwise.    I've not used any statistical functions other than average so I'm not sure how to do this.  Any thoughts or insights are appreciated.

[v-frfei-msft]

Hi @Clint ,

I have created a sample for your reference. Please have a check as below.

Measure = 
VAR maxxa =
    MAXX ( ALLSELECTED ( 'Table' ), CALCULATE ( AVERAGE ( 'Table'[Low] ) ) )
VAR ave =
    AVERAGE ( 'Table'[Low] )
VAR resu =
    IF ( maxxa = ave, BLANK (), ave )
RETURN
    IF (
        ISFILTERED ( 'Table'[category] ),
        resu,
        DIVIDE (
            SUMX ( ALLSELECTED ( 'Table' ), resu ),
            DISTINCTCOUNT ( 'Table'[category] ) - 1
        )
    )

For more details, please check the pbix as attached.

[Clint]

Hi @v-frfei-msft ,

Thank you for this.  If I'm reading this right, this measure will replace the Max Average in each category w/a "blank"?  Assuming I understand this correctly, that's a good start but what I really need to do is figure out how to strip out all outlier values.  Ideally, I'd like to create a table this removes any values 2 standard deviations above the mean or, if that's not possible, any values above the 95 percentile.

[v-frfei-msft · Accepted solution]

Hi @Clint ,

Does that meet your requirement?

Measure 2 = 
VAR k =
    SUMMARIZECOLUMNS ( 'Table'[category], "ave", AVERAGE ( 'Table'[Low] ) )
VAR a =
    CALCULATE ( STDEV.P ( 'Table'[Low] ), ALLSELECTED ( 'Table' ) ) * 2
VAR fk =
    FILTER ( k, [ave] < a )
VAR resu =
    IF ( AVERAGE ( 'Table'[Low] ) < a, AVERAGE ( 'Table'[Low] ), BLANK () )
RETURN
    IF (
        ISINSCOPE ( 'Table'[category] ),
        resu,
        DIVIDE ( SUMX ( fk, [ave] ), COUNTROWS ( fk ) )
    )

Pbix as attached.