# Standard deviation of sum per month in PowerPivot Excel

• ### Question

• Hello

First of all I am not a verified user yet so bear with me as I cannot upload links or images. I want to calculate the standard deviation per month in a Excel Power Pivot 2016 table based on the sum of the tables below. Let me clarify the tables.

Let's say October consists of 23 workdays on which the product can be sold. If the product was sold everyday, the sum would be 1. In this example the product is sold 5 times in October, so the sum would be 0,217391 (5*1/23). I let Power Pivot calculate this for me based on the sum in the values window.

Now I would like to have the standard deviation between the sums per months. The stdevp function asks me to enter a column, but that is not what I have here. Is there a way this can be calculated using PowerPivot?

 Oktober Times product 1 sold 5/10/2020 0,043478261 6/10/2020 0,043478261 8/10/2020 0,043478261 13/10/2020 0,043478261 15/10/2020 0,043478261 Oktober: 23 workdays (1/23 = 0,043478261) Count= 5 (0,043478261*5=0,217391304) November Times product 1 sold 1/11/2020 0,043478261 5/11/2020 0,043478261 7/11/2020 0,043478261 15/11/2020 0,043478261 Count= 4 November: 23 workdays December Times product 1 sold 2/12/2020 0,045454545 3/12/2020 0,045454545 9/12/2020 0,045454545 11/12/2020 0,045454545 Count= 4 December: 22 workdays

Power Pivot SUM
desired result
2020_10    2020_11    2020_12 Stdevp=
product 1 0,217391304 0,173913043 0,181818182 0,018910013
Tuesday, April 14, 2020 7:56 AM

### All replies

• Excel 365 Pro Plus with PowerPivot and Power Query.
the number of normal working days of a month,
the actual number of days worked in a month,
the set of ratios of those two numbers,
and the standard deviation of that set.
"...ye who enter here."
http://www.mediafire.com/file/rcg36g6fttsi0ta/04_06_20.xlsx/file
http://www.mediafire.com/file/f5i8mk8ppta5ukg/04_06_20.pdf/file

• Marked as answer by Friday, April 17, 2020 5:33 AM
Tuesday, April 14, 2020 7:48 PM
• This is great, thank you. But is it also possible when you use more than 1 product?
Friday, April 17, 2020 5:34 AM
• Just add a "Product" column and a Slicer
and tweak the dozen functions now affected.