Pivot Report Running Total

Originally Posted by 6StringJazzer

I can't quite get how your pivot table is set up from your explanation. I had no trouble with this, as in the attached example. If you attach your workbook it may be easier to see what your situation is.

This is actually apart of a large question. I can't attach the actual spreadsheet because its sensitive info. But I've attached something similiar. I want the second column instead of listing the percentage to list a cumulative percentage. First column would read 31.75 second one would read 62.5 etc. If I set it up this way even when the user entered another category it would dynamically update the chart. The final goal is to make a pareto with the pivot chart.

Originally Posted by 6StringJazzer

Aha. Your first post didn't refer to percentages. I have not found a way to get Excel pivot tables to do what you want. It offers percentages, or running totals, but not running totals of percentages. The way I would do this is to simply add another column outside the pivot table that has formulas to do a running total. However, if new categories appear, that column will have to be extended manually (unless VBA comes into play). The formulas wouldn't have to change, just be copied down to new rows.

My specialty is "not elegant, but easy and it works."

Hi Thanks for the help. I was afraid that I would have to do the sheet like this. However I though tI would be able to make a running total so the table red 20, 40 and then 63. At his point I would have a calculated row that would divide the running total column by the sum of the quantity column. I tried this however the column running total returned "NA". This seems to work in my head and if I could get the running total column to not return NA I could try it out. Do you know why running total would return NA in this case? In Count of Quantity 2(on my spreadsheet)?

Thanks for your help by the way!

Hi jdm2008,

I have just spent a few days trying to ferret out these pivot columns and Show As.. selections.

I think I have it on the attached. I believe 2010 Excel makes the access to the options a little more understandable.

See if this is what you want.

Hi,
Here are the steps using Excel 2010.
Right Click on D18. Then click on "Show Values As -> More Options.
There is a dropdown and I select %Running Totals In and in a Base field select Category.

I've read chapters about this stuff and it confuses the heck out of me. The word Item is used along with Field interchangeably. It seems they confuse Row and Column sometimes. There is a new selection of Previous and Next that I'm still learning.

I believe you should be able to do the same things in 2007.

I hate it when some guy finds an answer I thought was impossible too.

hope this helps.

See what 2010 has is new Pivot Table options at
http://blogs.msdn.com/b/excel/archiv...xcel-2010.aspx

Sorry for not knowing enough about older versions of Excel. At least this gives the OP an option to upgrade if they really need the functionality.