Weighted average in PivotTable

Question

I have a question which I just can't figure out. I have a pivot table and want to create a weighted average from that data. I have researched and people suggest to do this by using a calculated field. However, my data is structured differently, I have one measure per column. This is what my data looks like:

City        Product Measure Amount
Miami       Apple   Price   $4.0
Miami       Apple   Rating  50.0%
Miami       Pear    Price   $3.00
Miami       Pear    Rating  15.0%
Miami       Banana  Price   $13.00
Miami       Banana  Rating  85.0%
New York    Apple   Price   $2.00
New York    Apple   Rating  75.0%
New York    Pear    Price   $11.00
New York    Pear    Rating  15.0%
New York    Banana  Price   $9.00
New York    Banana  Rating  10.0%

This is what my pivot table looks like right now:

Obviously the weighted average of the Rating is different than the regular average. Is there a way to get the weighted averages in the sum fields? I don't even need the Apple, Banana and Pear in the columns, in the end what I want to achieve is just show the cities (New York, Miami) and their weighted averages.

Answer 1

What you need is you need your weighting column that lines up with price so that it looks like this:

City    Product Measure $ Ammount   % to Total
Miami   Apple   Price       $4.00   50.00%
Miami   Pear    Price       $3.00   15.00%
Miami   Banana  Price       $13.00  85.00%
New York    Apple   Price   $2.00   75.00%
New York    Pear    Price   $11.00  15.00%
New York    Banana  Price   $9.00   10.00%

You can then add in the calculated Weighted average in the pivot table by telling excel that it's the $ Amount * the percentage

You can also use the sumproduct formula for a weighted average. In this example write =SUMPRODUCT(D2:D7,E2:E7)