Hi everyone,

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 (I added the manually calculated weighted averages on the right):

weightedaek08.jpg

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.

Thanks,

Robert