How do I get the proper average in a pivot based on pivot data?

Question

I'm trying to get the average number of "on time shipment" based on items rolled up to "ship numbers" and then by "order number". I have one order number in this scenario that is shipped via multiple shipments. It seems to me that after rolling it up via PowerPivot and then creating a pivot table, it's calculating the average based on the total lines of the "order number" instead the pivot.

PowerPivot Data:

Pivot based on data above:

How can I get the average number based on the pivot table rather than the PowerPivot total data of the order number? I'm probably not making any sense, but hopefully the images below explain it better. As you can see, when you roll up the items by ship number then by order number, you'll see that the actual average is 0.6 but the pivot is showing 0.5.

Help!

Answer 1

Technically speaking, the average is correct - if you look at the source data, for some reason all rows are duplicated and if you do regular average calculation, it's actually 0.5.

What you are looking for is calculating average for distinct values, which can be done easily with AVERAGEX function.

I have copied your table and created those 2 Calculated Fields (in Excel 2010, it's Measures):

Average on Time:

=AVERAGE(Table1[On Time])

Average on Time (UNIQUE)

=AVERAGEX(VALUES(Table1[Ship Number]), [Average on Time])

Using AverageX with VALUES() function makes it easier to calculate any expression ONLY for unique values.

If you then put both measures on PivotTable, you should get this:

First column is same as yours (using "regular" AVERAGE function). The second one shows the average calculated over distinct (unique) values of Ship Numbers.

Hope this helps.

PS: This great article by Kasper de Jonge helped me quite a bit with similar scenarios.