Include Filtered Items in Totals

Include Filtered Items in Totals is a very useful option that we can find in pivot table settings, and it allows us to display the correct total for values ​​in rows or columns that we have previously filtered. By using the DAX expressions we can most certainly reset the filters and thus calculate the correct sums, but sometimes there is no need to do this. Only by selecting/deselecting this simple option, we can get the correct totals in the report.

We have created a simple pivot table where we track sales by quantities sold. We have added a PROIZVOĐAČ (MANUFACTURER) report dimension as a slider and filtered the report to show „Apatin Brewery“ products.

By going to the Analyze ribbon and then clicking Options will open a pivot table options window. Select the Totals & Filters tab and then check the Include Filtered Items in Totals option. It is also advisable to check the Mark totals with * option to mark with asterisk any “incorrect totals”. By clicking OK we confirm the choice, let’s see how this works!

We will apply a Top 5 filter over the selected items in rows to show the 5 best selling products, and then sort them by quantities in descending order. If you pay attention to the values ​​in the queues and Grand Total you will see that something is utterly wrong – the total value is much higher! Grand Total actually represents the sum of all quantities sold for „Apatin brewery“ items. And there is an “asterisk” to tell us that row total is not correct.

If we want to see the row totals ​​we need to select Design ribbon and then deselect Include Filtered Items in Totals from the Subtotals menu, which actually temporarily overrides the previous setting.

To conclude, activating the Include Filtered Items in Totals option in the pivot table settings displays the total amount, according to the specified filter. By suspending this option, the Grand Total displays the sum of the values displayed in the rows.

Pivot table options contain yet another useful feature: Include filtered items in set totals. It works the same way, referring to the data we have grouped into sets within the Power Pivot Report.