Calculating A Weighted Average In a Pivot Table

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!