Standard deviation per category per year

Question

I have data where I need to calculate the standard deviation of units per category (apo key+ material key make a unique category for my data) per year (which in my case is observed as previous year (PY), current year (CY) and new year (NY) and is available in the column 'is year')

I have calculated the average using the following formula:

Average =
CALCULATE (AVERAGE (tab[units] ),
FILTER ('tab', 'tab'[Material - Key]=EARLIER( 'tab'[Material - Key]) && 'tab'[ISYEAR]=EARLIER('tab'[ISYEAR]) && 'tab'[APO Key]= EARLIER('tab'[APO Key]))
)

and now need to calculate standard deviation.
The STDEV.X uses the formula sqrt(sum(X-Xavg)^2/n) but my n has to change per category per year and so I cannot apply this straightaway. I have tried modifying the above formula for calculating average to calculate standard deviation too but it doesn't give the right values.

My dataset (the 'sd' column is what I want to get):

So what defines the category? Can you make a little example of one calculation so we understand better what yo are looking for. e.g. how the 1.0625 is calculated? — Aldert

William Rodriguez William Rodriguez · Accepted Answer · 2019-07-27T03:23:03

If I understood you correctly, the excel formula for row four above (i.e., the SD is 1.0625) should be: SQRT( ( (20-15.75) ^ 2 ) / 4 ) = 2.125

If that is correct, then the following should help you:

VAR __KEY = [Apo Key] & [Material Key] & [Is Year] 
VAR __AVG =  
    AVERAGEX(
        FILTER(
            Tab , 
            __KEY = [Apo Key] & [Material Key] & [Is Year] 
        ) , [UNITS]
    )
VAR __N = 
    COUNTX(
        FILTER(
            Tab , 
            __KEY = [Apo Key] & [Material Key] & [Is Year] 
        ) , [UNITS]
    )
VAR __SD_Manual = SQRT( DIVIDE((([UNITS] - __AVG)^2),__N) )
RETURN __SD_Manual

Throw this into a calculated column, and you should be good to go!

Standard deviation per category per year

1 Answers