# Pivot Table "% Running Total" calculating incorrectly (XL365)

#### AOB

##### Well-known Member
Hey there,

I have a simple pivot table with a "Count" value field against a list of categories. I want to use the pivot table to produce a pareto chart (cumulative percentage as a secondary series) which I would normally do with my own table / calculated column. However, the underlying data is dynamic, refreshed via a data connection, so I would prefer to just refresh the data, and then refresh the pivot, and automatically include & sort any new categories in the chart, rather than having to manually copy and paste the pivot data into an independent table (or write VBA to do it - which I could do but would just prefer not to in this instance)

I figured adding a "% Running Total In" column as another value field would do the trick here - on the face of it, it should be doing exactly what any formula I would write, would be doing anyway (i.e. calculate the percentage of the total, then cumulatively add the percentage in a sorted list)

What's weird is - the numbers in the calculated column, aren't what I calculate them to be manually? Which is now making me wonder - what on earth does Excel think it's calculating here?

For example, here is a sample output from my pivot table :

Category​
Count​
% Running Total In​
My Own Calculation (outside PT)
A
498​
63%​
60%
B
99​
75%​
72%
C
83​
85%​
82%
D
59​
91%​
89%
E
36​
95%​
93%
F
29​
97%​
97%
G
25​
100%​
100%
Grand Total
829

By my maths, for category A, the "% Running Total In" should be 60% (498 / 829) - where is Excel getting 63% from?

Similarly, for category B, the "% Running Total In" should be 72% (99 / 829 = 12% + the 60% from category A) - again, where is Excel producing the 75% from?

Basically, I'd just like to know if I'm misinterpreting what "% Running Total In" is supposed to actually do? What's throwing me here is the fact that we both end up at 100% - but the "steps" in between don't make any sense?

Thanks!

AOB

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Are you sure your running total field is set to Count?

AOB
Are you sure your running total field is set to Count?
Well you learn something new every day...!

I always thought, with no basis whatsoever, that "Summarize Values By" and "Show Values As" where independent of each other! ?

It had defaulted to Sum when I created it and I presumed that was irrelevant as I was moving across to a calculated field.

Setting this to Count works a charm.

Thanks @RoryA that's great, thank you!

Replies
6
Views
199
Replies
2
Views
284
Replies
1
Views
664
Replies
3
Views
150
Replies
4
Views
3K

1,219,791
Messages
6,150,285
Members
450,949
Latest member
faizanmalik10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back