Hello - This is hopefully a dumb question - but I have yet to figure out how to create a PT that sums individual columns as a GRAND Total AND concurrently displays the % of the Total immediately below in the next row. Here is a picture of what I want to create (that works with Slicers, etc.) - with a manual % calculation below. Ok - I couldn't figure out how to paste a picture.
Something that looks like this:
Labels Level 1 level 2 level 3 Grand Total
Subject A 3 4 2 9
Subject B 4 30 58 92
Subject C 0 16 11 27
Grand Total 7 50 71 128
% of Total 5.5% 39.1% 55.5%
I know I could make two identical Pivot Tables near each other in a sheet - one displaying grand totals and an identical one displaying % of total - with a multi-select report Slicer to make them filter together - but that really seems an odd way to do it - has to be a way.
I can't say I've closely reviewed every "Xtreme Pivot Table" lesson (again) - but if you could point me where I could learn / re-learn how to execute this simple example - it would really help me in my day job.
Thank you!
Max
Hi Max,
I know it seems simple, but that's not how PivotTables are designed. They can't distinguish between showing the breakdown for some calculations and not for others, or add additional total rows.
You could add the values again and set the second set to calculate percentages and then collapse the subject level for that group, so you only see the total line.
If you're stuck, upload a small example Excel file and we can help you further.
Mynda
Thank you Mynda for confirming my understanding... was just hoping that I had missed something... 🙂
If you're using the data model, you could also create a set that only shows the second calculation at the grand total level, not the individual rows.
