cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Resolver III

## Standard deviation using "CALCULATE / FILTER" fx

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])))`

1 ACCEPTED SOLUTION
Employee

@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,

2 REPLIES 2
Employee

@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,

Resolver III

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