Forum

Standard deviation ...
 
Notifications
Clear all

Standard deviation of sum per month in PowerQuery Excel

5 Posts
3 Users
0 Reactions
354 Views
(@nick-eykens)
Posts: 2
New Member
Topic starter
 

Hello

I really like your video's and I hope you can help me with my problem.

I want to calculate the standard deviation of the sum per month in a Excel Power Pivot 2016 table. Now if I use the standard deviation it will take the standard deviation of the underlying tables. I want the standard deviations of the sums per month, so in this case: STDEV.P(63.75;38,13636364;20,54545455) with the result: 17,739.

Material Year_Month Som van Picks_Total Stdevp van Picks_Total  
795300 2019_11 63,75 #GETAL!  
  2019_12 38,13636364 #GETAL!  
  2020_01 20,54545455 #GETAL! desired result
Totaal 795300   122,4318182 0,018309092 17,739

I added the file in attachment and also a link to the file below. Thanks in advance.

http://www.mediafire.com/file/grkimfqqpra4737/Example_Std.xlsx/file

 
Posted : 22/04/2020 3:24 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Nick,

Welcome to our forum! Thanks for sharing your file. You can do this in Power Query (see Sources (2) in file attached) but it only has the ability to do the equivalent a STDEV.S formula i.e. standard deviation of a sample.

In Power Pivot we have STDEVX.P which is the equivalent of Excel's STDEV.P function, but  you need to first SUMMARIZE the data into a table that groups the data by material and month. I've added the following measures to your file:

Total Picks: =SUM(Sources[Picks_Total])

And

StdDev: =STDEVX.P(
SUMMARIZE(Sources,[Material],[Year_Month],"Grouped",[Total Picks]),
[Grouped])

Tip, you should move the Picks calculation to Power Query as this is more efficient than adding a calculated column in Power Pivot.

I hope that helps.

Mynda

 
Posted : 22/04/2020 6:50 am
(@nick-eykens)
Posts: 2
New Member
Topic starter
 

Hi Mynda

 

This is exactly what I wanted. Thanks a lot!

Also, I'll use your tip in my current and future files!

 

Kind regards

Nick

 
Posted : 23/04/2020 1:37 am
(@steveo)
Posts: 26
Eminent Member
 

Mynda,

For various topics posted, I try to recreate the solutions offered.  I started with Nick's Nov, Dec and Jan data, and recreated going step by step after printing out the advanced editor from your Sources 2 query to see if I can get to the solution you posted.

In the process I had to duplicate the Sources 2 query after the add column step to get the Workday query. 

The question I have is that I don't really see in the Advanced Editor an indicator when you needed to duplicate the query.  When looking through this how do you know which queries are duplicated?

Thanks for your site and the many contributors.  Everyone is so generous,

 

Steve 

 
Posted : 26/04/2020 2:07 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Steve,

It's great to see you're practicing using the examples here. I simply duplicated the Sources query to preserve Nick's original query, in case he wanted to refer back to it. 

Mynda

 
Posted : 26/04/2020 7:40 pm
Share: