Standard Deviation for QTY sold by yearly basis

[NetromEner]

Hi,

I am trying to migrate from Excel to power BI and need some help with the DAX. I am trying to find the standard deviation based on yearly sales per product code. The goal is to accomplish somthing similar to below Excel version.

My data is structured like the below table and I want to find standard deviation for total QTY sold per year for each product, in this case  product no 33270

I got the yearly average and QTY sold per year sorted out in my report as shown below.

I really hope someone can guide me on my way.

Best Regards

Morten

[Anonymous · Accepted solution]

Hi  @NetromEner ,

Here are the steps you can follow：

1. Create calculated column.

STDEVXS =
STDEVX.S(FILTER(ALL('Table'),'Table'[Product Code]=EARLIER('Table'[Product Code])&&'Table'[Year]=EARLIER('Table'[Year])),[QTY])

STDEVXP =
STDEVX.P(FILTER(ALL('Table'),'Table'[Product Code]=EARLIER('Table'[Product Code])&&'Table'[Year]=EARLIER('Table'[Year])),[QTY])

2. Result:

STDEVX.S formula：

√[∑(x - x̃)2/(n-1)]

STDEVX.S function (DAX) - DAX | Microsoft Learn

STDEVX.P formula：

√[∑（x - x̃）2/n]

STDEVX.P function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[zerotyper]

@NetromEner  The Dax also has the specific function to calculate the Standard Deviation