cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataDiva Regular Visitor
Regular Visitor

dynamic standard deviation

Hi! Someone may have provided a solution to this problem somewhere, but I haven't found it yet... 

 

I need to create a multi-layered dynamic standard deviation calculation that helps my users identify outliers in their data. The problem that I am running into is that I clearly don't understand DAX enough to get the calculations to work at the correct levels of aggregation. I have a table like this:

 

Date        Hospital ID     LineItem     LineValue

1/1/17     700                   Births          56

1/1/17     700                   ED Visits     108

1/1/17     800                   Births          409

1/1/17     800                   ED Visits     10083

2/1/17     700                   Births          61

2/1/17     700                   ED Visits     120

2/1/17     800                   Births          423

2/1/17     800                   ED Visits     10165

 

I need to be able to compare any given value for any given facility for any given month to the average for the previous twelve months, and the standard deviation for that 12 months. And THEN I need to be able to have something that will allow me to select all the values that are outliers and call them out for investigation. 

 

I'm not sure this is possible, since it seems like the filter needed for the single month selection is going to interfer with the 12 month selection, and then the outlier filter (to select those that are more than, say, 3 standard deviations from the 12 month average) is going to further screw up all the calculations. 

 

Any ideas...????

4 REPLIES 4
Highlighted
DataDiva Regular Visitor
Regular Visitor

Re: dynamic standard deviation

To clarify, what I need is:

 

for 1/1/17 for hospital 700 for births, the value is 56, the average is 90, the standard deviation is 4 so this is an outlier value. Then I want a table that just has all the hospitals and dates and LineItems that are outliers only.

Community Support Team
Community Support Team

Re: dynamic standard deviation

HI @DataDiva,

 

I think you can refer to below formula if it suitable for your requirement.

 

Sample:

1. Calculation rolling 12 month standard deviation.

 

Rolling 12 Month SD LineValue =
CALCULATE (
    STDEV.P ( Table[LineValue] ),
    FILTER (
        ALL ( Table ),
        Table[Date]
            >= DATE ( YEAR ( [Date] ), MONTH ( [Date] ) - 12, DAY ( [Date] ) )
            && Table[Date] < EARLIER ( [Date] )
            && Table[Hospital ID] = EARLIER ( Table[Hospital ID] )
            && Table[LineItem] = EARLIER ( Table[LineItem] )
    )
)

 

2. Loop through above column to tag unusually records.

Tag =
VAR defineValue = 1
VAR result =
    CALCULATE (
        STDEV.P ( Table[Rolling 12 Month SD LineValue] ),
        FILTER (
            ALL ( Table ),
            Table[Date]
                >= [Date] - 3
                && Table[Date] < EARLIER ( [Date] )
                && Table[Hospital ID] = EARLIER ( Table[Hospital ID] )
                && Table[LineItem] = EARLIER ( Table[LineItem] )
        )
    )
RETURN
    IF ( result > defineValue, "Y", "N" )

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
DataDiva Regular Visitor
Regular Visitor

Re: dynamic standard deviation

Thank you for your suggestion. However, I tried the first calculation and got, "Ealier/earlist refers to an ealier row context which doesn't exist." 

 

Can you explain how this calculation is supposed to work?

Community Support Team
Community Support Team

Re: dynamic standard deviation

Hi @DataDiva,

 

According to the error message, it sound like your columns not from in same table, right?
If this is a case, earlier function not works for this scenario.

 

Can you please share some sample data or pbix file for test? It will help for clarify table structure and coding formula.

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 363 members 3,314 guests
Please welcome our newest community members: