Standard deviation using "CALCULATE / FILTER" fx

[Arnault_]

Dear All,

I am trying to calculate the standard deviation of my sales per product.

I managed to get it per order, but I wanted to consolidate sales per month. Then I created periods equivalent to "Month" quite easely and got the expected results. The problem is when there is no order during a month. I want to take into account the months without sales.

I am quite sure there are more sophisticated and better approach than mine, but here is what I have done:

I decided to create a table with all my products reference and calculate the sales per period:

- The first column with the product reference ("ART/VER")

- The second column with the period 1 ("2017-1"))

- The second column with the period 2 ("2017-2")

- ...

I am stuck with the last step, where I want to calculate the standard deviation for each product. I got "0" when using "EARLIER" and if not, I got the standard deviation over the all products. What's the problem?  Thanks in advance.

I have used the following formual :

VARIABILITE = CALCULATE(STDEVX.P(VARIABILITE;VARIABILITE[20171]+VARIABILITE[20172]);FILTER(VARIABILITE;VARIABILITE[ART / VER]=EARLIER(VARIABILITE[ART / VER])))

[v-sihou-msft · Accepted solution]

@Arnault_

In this scenario, you should modeling your data with 3 columns: Product, Month, Sales. 

Then you can create a your standard deviation measure like: 

=STDEVX.P(VALUES(Table[Month]),[Sales Measure])

You can put your FILTER in that [Sales Measure]. When you put Product on Rows, it will slice above measure properly.

For more details, please refer to article below:

https://powerpivotpro.com/2013/10/standard-deviation-demystified-in-power-pivot/

Regards,

[Arnault_]

Hi, this is not exactly what I was looking for, but it might help other people. Thanks.