Forum Discussion
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])))
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
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,
Hi, this is not exactly what I was looking for, but it might help other people. Thanks.
