Pivot Table subtotals and grant totals blank
Hi,
I’m running into an issue where the pivot tables I create aren’t showing sub totals or grand totals when fields are placed into the ROWS section. Maybe I missed some sort of update but typically when you have a number field pivot tables create subtotals at the bottom or top of the without needing to duplicate that field in the values section and grand totals and the end, right?
I’ve attached an example spreadsheet showing the issue, the data is there and this is a brand new blank spreadsheet without any changes to formatting, all numbers are in “general” format.
Is there some sort of setting that I accidentally shortcutted to break pivot subtotal calculations?
Link to screenshot: https://imgur.com/a/L3XKelb
Thanks,
Posted Jan 30, 2019, 7:14 PM UTC
It sounds like you just need to move the total sf field to the values area. A pivot table won't show sub/grand totals of anything unless you have something in the values area.
Well that doesn't make any sense, what would be the purpose of the subtotal and grand total rows without anything in the values area?
I added the same field to the values section and the subtotals appear but it looks redundant/messy duplicate information
https://imgur.com/a/yoMLlSt
I have a hard time believing this is intentional! I certainly hope not!
Exactly, the screen shot of the pivot table you included in your initial post didnt have anything in the values area. It looks clunky because you still have the total sf field in both the rows and values area. Remove the total sf field from the rows area and you should be good to go.
In that case, since the total sf isn't included in in the rows section all I'm able to get the is the sum of each BU, without showing the individual numbers that were added into it.
https://imgur.com/a/QYR3Mug
Call me crazy but I could have sworn that the standard in excel was to add sub totals and grand totals when numerical fields were added into the ROWS section without requiring it in the values section.
Thanks for all the help so far!
I don't recall that being an option without having it in the values section. This probably isnt exactly what you're looking for but one other way to clean up some of the redundancy would be to change the report layout to either outline or compact which would at least remove the "11-CA-10xx Total" labels. I wish I had a better alternative for you though.