locked
Standard deviation of sum per month in PowerPivot Excel RRS feed

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

    Thanks in advance.


    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

Answers

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Added to the solution of your previous 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 NeYk 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.
    Same links.

    Friday, April 17, 2020 8:57 PM
  • Your file is unusable since it has many external links not inaccessible to me.
    It does not show that you have, used or understood PowerPivot.
    My solutions are at the top level of PP/PQ and are thus inaccessible to you.
    No mas.

    Tuesday, April 21, 2020 1:21 AM