How do I get a pivot chart to show me a running total as a percentage

jsimonuchc
jsimonuchc used Ask the Experts™
on
Hi,

I am trying to figure out a way to get a pivot chart to give me a running total as a percentage. I know how to make it give me a running total as a count, but I can't seem to make it do it as a percentage. For example, this is what I would like to do with my pivot table.

 Item                 Count            Running Total            Running Total Percent
Oranges            25            25                          21.19%
Apples            51            76                            64.41%
Peaches            21            97                          82.20%      
Grapes            16            113                          95.76%
Bananas            5            118                         100.00%

Total            118            

I would like to do this without VBA. Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello jsimonuchc,

You could add a 'Running Total Percent' field to your source data with the formula

=Total/SUM(Total)

Then add the field '% of Total' in your PivotTable and use the "Running Total in" option as you did for your Running Total field. Format as 0%.
Be sure to Enable selection before applying this format so that it will persist after Refresh.

Regards,

teylyn

Author

Commented:
I should have mentioned the other caveat. I can't modify the source data. (Client's will be providing me this data as is)
Can you paste a screenshot of pivot table that you have.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Unfortunately, I cannot. The pivot table i have has protected data on it. The sample I provided is a very close approximation of what the pivot table looks like,
Top Expert 2010
Commented:
Hello jsimonuchc,

Unfortunately, the answer is that this cannot be done within the PivotTable.  You would have to use a formua
outside the PivotTable to do this, which would of course mean that if the PivotTable resizes your formulas
could be wiped out or become outdated.  That risk, however, could be mitigated by using VBA code on the
worksheet's PivotTableUpdate event.

Regards,

Patrick

Author

Commented:
Ok Thanks. I think they address this problem in Excel 2010.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial