Forum Discussion
Excel Parent Grand total instead of Grand Total for Pivot Table
Definitely possible. Could you attach an actual spreadsheet rather than just an image? That would help anybody here help you. With an image -- despite the saying about pictures and how many words they're worth -- one can only assure you that what you're asking is possible.
An image leaves some questions unanswered: where's the raw data on the basis of which those percentages are being calculated? And I assume that you're saying the figures in green in any given section should be percentages of the dark orange off to the right (the pale one is close enough to the orange part of the spectrum to raise that question).......
Anyway, posting an actual spreadsheet would help us give you the actual formulas.
You hadn't mentioned that it was a Pivot Table in your first post. So I played around with the field setting (right click on the field in the pivot table area, and then pick "Show Data As")
I am not sure if this is what you were looking for, but I am pretty sure that you'd find the result by playing around with that setting.
If that's not it, come back and clarify.
- Thank you for the response! I've tried that but it'll give the % of grand total for the entirety of the data, I'm looking to pick the base for % of grand total, but similar to your screenshot, it was greyed out
What about the other settings there, under the "Show Data As" dialog? I wasn't exactly sure what you were looking for, but did see that there were some options that offered the choice of Level 2, Level 3 and so on.
Anyway, as I said, I played around with the Pivot Table, once I learned that was what you were using. It's specifically the playing around that I was trying to emphasize. In my experience with Pivot Table, the default settings usually work; when they don't some tinkering is called for and it's not always intuitive, but it always has come through with patience.
If in fact none of the many settings there work as you want (which would surprise me, given how many there are), then I can conceive of some other ways to approach it that would require the newest Dynamic Array features (UNIQUE, FILTER, SORT), which do work in Excel for Mac but only the newest version. So if you want to pursue that, please confirm that the raw data in your sample are arrayed as are the data in your real case. And let us know what version of Excel you're working with.