Calculating correct STDEV (including blanks)

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

I’m trying to create a measure to calculate the “correct” standard deviation over the quantity of products that have been scanned out per week.

Zoom in on the problem:
<table width=”208″>
<tbody>
<tr>
<td width=”80″>WeekNum</td>
<td width=”64″>ProdID</td>
<td width=”64″>Quantity</td>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>5</td>
</tr>
<tr>
<td>3</td>
<td>1</td>
<td>10</td>
</tr>
<tr>
<td>8</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>10</td>
<td>1</td>
<td>5</td>
</tr>
<tr>
<td>52</td>
<td>1</td>
<td>5</td>
</tr>
</tbody>
</table>
In this case, the correct calculation of average per week and stdev over quantities per week are:

• Average: 0,5 (i.e. 26/52) and not 5,2 (i.e. 26/4)
• Standard Deviation: 1,79 and not 3,19

The data:
<table width=”272″>
<tbody>
<tr>
<td width=”71″>ProdId</td>
<td width=”87″>Quantm3</td>
<td width=”114″>ScanOut</td>
</tr>
<tr>
<td>40</td>
<td>1,596</td>
<td>11/07/2017 10:37</td>
</tr>
</tbody>
</table>
Each transaction in “Facts” is identified by:

• The product id (ProdId)
• The consumed quantity im m³(Quantm3)
• The date of consumption (ScanOut)

My steps towards a (not) working report:

• Bring consumed quantities per product together with the year and week number of  the “consumption date”
• Filter this table (only keep the data of last year – I’m not interested in earlier consumption)
• Aggregate (STDEV  /AVERAGE)

This brings me to next formula:

Average per week (Summarize Columns):=AverageX(
SUMMARIZECOLUMNS (
‘Calendar'[Year];
‘Calendar'[Week];
‘Facts'[ProdId];
“Quantity”; IGNORE ( SUM ( ‘Facts'[Quantm3] ) )
);[Quantity])

When I try to add this measure on my pivot table report in Excel, I receive next error:

“SummarizeColumns() and AddMissingItems() may not be used in this context.”

Translating this formula to a normal “Summarize” formula leaves out all the blank lines (because the IGNORE formula can’t be used). This results in incorrect calculations of AVG and STDEV (see introduction).

Anyone who can help me out on this?

Attachments:

You must be logged in to view attached files.

[arnebrackeParticipant Started: 3Replies: 4Total: 7]

Text version (instead of HTML-converted version) of the tables used in my explanation:

Example of the incorrect calculated average:

WeekNum    ProdID    Quantity
1                      1                5
3                      1                10
8                      1                1
10                     1                5
52                      1                5

Weekly average Quantity for Product 1: 26/52 = 0,5
Mathematic average of above table: 26/5

Design of data table:

ProdId    Quantm3    ScanOut
40            1,596            11/07/2017 10:37

[Author]

Posts