Selective Sub-totals in Pivot Tables [Quick Tip]
Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like this:
How to show selective sub-totals in Pivot Tables
First instinct suggests that using Design ribbon > Sub-totals we can tell pivot table how we want the sub-totals.But this is like a master switch. It will turn off or on all sub-totals in the report. What we need is sub-totals only at one level of the report (in this case, sub-totals by department, but not by month).
The answer is simple.
- Click on any month label. This will select all months in the row label area.
- Right click > Field settings (you can also access this from Analyze Ribbon > Field Settings)
- In the “Subtotals & Filters” tab, choose “None”
- You are done.
Happy Pivoting. I am off to a short spin on my cycle before heading to work.
Introducing our Online Power BI Class:
Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.Click here to know more and join us.
Leave a Reply
|Avoid Hiring Boo-boos with Excel – COUNTIFS for the win [video]||Start your bar charts from zero – Excel geeks screaming at you from mountain top|