weighted average % calculation in pivot table

**maymano** · 08-28-2013, 04:55 PM

Hi, this is I think a simple question but it is puzzling me.

I want to do a weighted average calculation in a pivot table. I have different product categories (call them A,B ,C etc). I have profit in an excel sheet for each product sold by customer and date. In the sheet I have a column calculated for margin % ((sales - cost)/sales) each time a product category is sold. In the Pivot table however, it appears that Excel only does a simple average of this column.

I thought I could solve the problem by using the actual margin (sales - cost) and then summing it in the pivot table and then dividing that by the total sales for each product category. I went to Value Field Settings, Show Value as and I tried to do it as a percentage of ... but I am not doing it correctly since I could not figure out how to use the sum of the sales value for A as the denominator or base item using the calculation tab. I have attached a simple sheet to show what I need. Thanks.EXAMPLE OF WEIGHT AVERAGE NEEDED.xlsx

**FDibbins** · 08-28-2013, 08:30 PM

Instead of a PT, you could create a simple table and use this, copied down and across...
=SUMIF($B$2:$B$7,$E16,C$2:C$7)
Then jusdt use the same formula you were using in the 1st table

**maymano** · 08-28-2013, 10:33 PM

Thanks - yes, that can work but I do have other reasons to wanting to use the pivot table. There are other sub categories I am analyzing too which include domestic and import etc. It is useful to have these collapsible sub fields as part of the table. I thought I could point to the Sales Value of A as the total for denominator and then take (Sum of Profit Margin for A / Total Sales Value for A as the base field) to get my %. That automatically takes into account the weighted average. Is there a way to do that in Pivot tables?