Calculating A Weighted Average In a Pivot Table

[Mvaldesi]

Good afternoon, folks!

Please see the attached workbook. I hope the layout of the pivot table is explanatory enough for you, but I’ll try my best to explain what I’m trying to do in words. We have Delivery Instruction numbers (hereafter “DIs”) that are created when allocations from a central inventory location are generated to replenish items sold out of our stores. The central location then ships the bulk allocations to 11 support centers (called the “SHIPPER” in the attached data) which then ship to the individual stores.

The metric I’m trying to determine is the average allocation duration, by SHIPPER, for each leg of the process between DI_CREAT_DATE and STR_ARRIVAL_DATE, then a weighted average of those averages, e.g.: one SHIPPER with a single DI that had a duration of 50 days unfairly weights when another SHIPPER that had 5 DIs with an average of 5 days duration, so the subtotal average is skewed.

I tried looking up using a weighted average in a pivot, and Google does make it seem like it can be done, but the instructions don’t marry up with what I’m seeing in my version of Excel (2016.) Can anyone offer some help or ideas?

Thanks in advance!

[Stormin']

Use a calculated column to do
SUM(DIs) / SUM(Days)
to get an average of DIs per day (or swap the calculation to get days per DI)


Note that you cannot calculate this line by line in the source data, it has to be aggregate data.

[Mvaldesi]

Use a calculated column to do
SUM(DIs) / SUM(Days)
to get an average of DIs per day (or swap the calculation to get days per DI)


Note that you cannot calculate this line by line in the source data, it has to be aggregate data.

Thanks for the reply, but I don't believe this is what I'm looking for. You're offering a straight average; I'm trying to get a weighted average, one which accounts for each SHIPPER's significance to the average before averaging for the whole network.

[sandy666]

read this and re-read post #2 Weighted Average in PT 2010
or
use PowerPivot / PowerQuery where you can define what you want in calculated columns or calculated fields
or (pattern)
col1*col2/col1

[Mvaldesi]

read this and re-read post #2 Weighted Average in PT 2010
or
use PowerPivot / PowerQuery where you can define what you want in calculated columns or calculated fields
or
col1*col2/col1

That link is exactly what I found, but I think I might have misread it; I'll give it another shot...

[sandy666]

Don't quote full post

[sandy666]

Or you can try this way:

[Stormin']

Ah, we are talking about different weighted averages. I actually opened your workbook this time

so the subtotal average is skewed

Subtotal averages in pivot tables are already weighted.

Another way of showing how much a shipper contributes to total duration is to show sum duration as % of grand total.


See attached workbook.

[Mvaldesi]

Subtotal averages in pivot tables are already weighted.

Well, hell, I see that now! Thanks for your help!