Standard Deviation of total sales

[lennox25]

Hi again :), 

I have been trying to get a standard deviation calculation in Power Bi. I dont seem to be getting it right.

This is my excel table. Any help really appreciated.

[Anonymous]

Thanks for the reply from Greg_Deckler, SamWiseOwl and GuillaumePower.

Hi @lennox25 ,

Based on the ideas SamWiseOwl provided, can you just create a calculated column to get the correct answer, here is my test result:

Column = STDEV.P('Table (2)'[Total Sales])

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[GuillaumePower]

StdDev =

// You have to use sumx on all the value from your table (with power,average and minus)

var LaSommeDesEcarts = sumx('Table',power((AVERAGE('Table'[valeurs])-'Table'[valeurs]),2))

// You divide on the number of elements

var NbElements=count('Table'[valeurs])

// You choose the sqrt

var Resultats = sqrt(divide(LaSommeDesEcarts,NbElements))

return Resultats

[GuillaumePower]

Ohhh, I don't have understood that you need to use the date in this calcul....

[SamWiseOwl · Accepted solution]

Hi @lennox25 

It would be something like this:

Std dev =

Var InitialDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),04,01)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))-1,04,01)

)

Var FinalDate =

If(

    month(SELECTEDVALUE('month avg'[Month Year])) >= 4

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year]))+1,03,31)

    ,Date(YEAR(SELECTEDVALUE('month avg'[Month Year])),03,31))

Return

 CALCULATE(STDEV.P('month avg'[Total Sales Per Month])

 ,ALLSELECTED('month avg'[Month Year])

 ,DATESBETWEEN(

    'month avg'[Month Year]

    ,InitialDate

    ,FinalDate

 ))

However I am unsure which StdDev you would need.

https://learn.microsoft.com/en-us/dax/stdev-s-function-dax Sample population

https://learn.microsoft.com/en-us/dax/stdev-p-function-dax Entire population

Then both have X variations for calculating the StdDev using an expression.

Apologises Greg_Deckler I did not see you had replied!

[lennox25]

Hi @SamWiseOwl  - any idea why its saying this? Im using my date table.

[SamWiseOwl]

Hi @lennox25 have you set your Month Year to be a Date data type?

[lennox25]

Hi @SamWiseOwl , yes but on doing that its now format for March 23 as 01/03/2023 - is this right?

The Std Deviation results are this as below, im not sure where Im going wrong? 

[Greg_Deckler]

@lennox25 Can you post data as text?

[lennox25]

MonthYear  Total Sales      Std Dev
Apr 23         67076             8,516
May 23        75713             8,516
Jun 23         63500             8,516
Jul 23          62006             8,516
Aug 23        73358             8,516
Sep 23        61440             8,516
Oct 23        76631             8,516
Nov 23       88816             8,516
Dec 23       77698             8,516
Jan 24        86106             8,516
Feb 24       78599             8,516
Mar 24      74128             8,516