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