Weighted Average in a Pivot Table

**Maverick 3672** · 04-23-2013, 04:02 PM

Hello everyone,

This is my first posting.

I have a table with Part #, Received qty, Merchant code, Received date, PO date, Product type & Lead time.

Received date - PO date will give me the lead time per row and when I do the pivot table for the entire data, I am able to get the average lead time per merchant, per product type. Is there a way to get the weighted average lead time per merchant, per product type? Please let me know.

Please see attached data.

**JosephP** · 04-23-2013, 05:36 PM

if I understand correctly add a new field in your source data that multiplies the lead time by qty. then create a calculated field in the pt that divides that new field by the qty field

**Maverick 3672** · 04-23-2013, 06:04 PM

Thank you so much. It worked perfectly.

Sorry to ask you, could you please explain the logic behind the steps you mentioned above? I feel so dumb!!

**JosephP** · 04-24-2013, 02:46 AM

the calc is sum(qty*lead time)/sum(lead time) for the weighted average. if you try and build that just using a calculated field the calculation you get is actually sum(qty)*sum(lead time)/sum(qty) which of course is just sum(lead time) so you need to add the weighting multiplication to the source data