Pivot Tables - Percentages in Row, Column, & Grand Totals


Posted by David Schwartz on October 26, 2001 4:34 AM

I am having a problem with calculated percentages showing up correctly in the row, column and grand total section of my pivot table. Instead of my percentages being recalculated based on the totalled data, my cell percentages themselves are being summed. Example: spreadsheet has 3 column headings: month, sold, returned. Under the headings are two rows of data: ROW 1 is jan,10,5. ROW 2 is feb,10,3. When I do the pivot table I put "month" as a column and "sold" and "returned" in the middle. Then I create a calculated field with formula = returned/sold. Thus, for January, my cell shows 10, 5 and 50%. In my row totals, I get 20 sold and 8 returned - which is correct. My percentage calculations in the individual cells work just fine but in the pivot table row total, my percentage shows as 80% (50% for Jan plus 30% for Feb), instead of 40% (8/20). How do I get around this to have correct percentages in my column, row and grand totals?

Posted by Mark W. on October 26, 2001 5:49 AM

David, I followed your PivotTable steps exactly
as you described and I get 40% (the correct
results) for the Grand Total -- not 80%. I
recommend that you re-create your Calculated
Field, and beyond that I don't know what to
tell you. Your steps work for me!



Posted by David on October 26, 2001 8:10 AM

Re: Pivot Tables - THANKS!

Mark - Thanks! I was actually doing something a step more complex than the example was showing.
That was what was messing up the calculation. Once I saw your response I did that part another way and it worked fine. Thanks for the help!!!