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

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
600
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,748
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Are you sure your running total field is set to Count?
 
  • Like
Reactions: AOB
Solution

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
600
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!
 

Forum statistics

Threads
1,147,632
Messages
5,742,229
Members
423,714
Latest member
ftp2jz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top