06 April 2017

Sub totals in Excel's Pivot Tables - which total should I sub?

This wonderful feature shows you why Pivot Tables (Power Query or other pivot table) wins hands-down vs Power BI, the much hyped MS app.
Let's say you have a Pivot Table (which I do, plenty of them).
And let's say they are summed by different values: Year, Quarter, and forecast vs. Actual (just an example). But you want to pick which columns (or rows) you want the sub totals to run on.
You can choose if you want to sub total all column or only few of them.
Let's look at an example:
This is what the header looks like:



Now I would like to have the total on the Year, but not on the Quarter.
First of all I need to go to PivotTalbe tools -> Design -> and pick the Subtotals that I want
( chose the Show all Sub Totals at the bottom of the group; you can pick top, but don't chose non).




I can go to the columns definition, right click the setting (FyQ, in my case) , choose the Field Settings, and then I have the choice of Subtotals: Automatic or None. So If I want it to be summed I would and if not then I don't.

The beauty here is that I don't need complicated MDX functions in order to design my Pivot table. Great!