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