#### AOB

##### Well-known Member

- Joined
- Dec 15, 2010

- Messages
- 600

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