Using DAX to spot statistical outliers and trends

[michaelmichael]

I've been developing reports that look at the average and standard deviations across a visual or table

I have written a simple DAX function to spot and highlight any outliers that go outside of the standard deviation lines:

Outlier = IF([Values]<[Average -3StDev],[Values],IF([Values]>[Average +3StDev],[Values],""))

What I'm now looking to do is spot when 2 or more outliers happen in a row, and highlight these.

So IF the current X-axis cell is an outlier AND the previous X-axis cell was an outlier, highlight BOTH by displaying the two Values.

I want to do similar functions for up to 7-9 cells in a row.

Any help is much appreciated

[v-joesh-msft]

Hi @michaelmichael ,

Try the measure below:

Measure =
VAR _lastrow =
    CALCULATE (
        MAX ( 'Table'[X-axis] ),
        FILTER ( ALL ( 'Table' ), 'Table'[X-axis] < MAX ( 'Table'[X-axis] ) )
    )
VAR _nextrow =
    CALCULATE (
        MIN ( 'Table'[X-axis] ),
        FILTER ( ALL ( 'Table' ), 'Table'[X-axis] > MAX ( 'Table'[X-axis] ) )
    )
VAR _lastoutlier =
    CALCULATE ( [Outlier], FILTER ( ALL ( 'Table' ), 'Table'[X-axis] = _lastrow ) )
VAR _nextoutlier =
    CALCULATE ( [Outlier], FILTER ( ALL ( 'Table' ), 'Table'[X-axis] = _nextrow ) )
RETURN
    IF (
        OR (
            [Outlier] * _lastoutlier
                <> BLANK (),
            [Outlier] * _nextoutlier
                <> BLANK ()
        ),
        1,
        2
    )

Then apply conditional formatting to Outlier:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EbAVkKFAjntEkAfRT7... 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michaelmichael]

@v-joesh-msft thanks for the response, and the demo.

I've got this measure active, but I can't add it into the table because of an error (110, 568) Cannot convert value "of type Text to type Numeric"

My measure for the outlier is as follows:

Outlier = IF ( [Values] < [Average -3StDev] , [Value] , IF ( [Values] > [Average +3StDev] , [Values] , "" ) )

The [Values] is another measure which is:

Values = IFERROR ( STDEV.S ( 'Table' [Numbers] ) , "" )

The Numbers column is set to data type = Decimal number and obviously the measures are set to = Variant

[v-joesh-msft]

Hi @michaelmichael ,

Sorry, I forgot to say, need to change Outlier to:

Outlier =
IF (
    [Values] < [Average -3StDev],
    [Values],
    IF ( [Values] > [Average +3StDev], [Values], BLANK () )
)

Use BLANK () instead of "" to see if it works.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[michaelmichael]

@v-joesh-msft the BLANK () worked, thanks

But now the formula doesn't create the 1's. I copied your formula straight in from your demo sheet

[v-joesh-msft]

Hi @michaelmichael ,

Could you share your sample pbix file for me to have a test, it would help tremendously.

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.