AOB
Wellknown Member
 Joined
 Dec 15, 2010
 Messages
 637
 Office Version

 365
 2016
 2013
 Platform

 Windows
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 :
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
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