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
Bookmarks