Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Arnault_
Resolver III
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])))

 

f.png

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
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,

 

 

 

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
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,

 

 

 

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.