Build a Better Top 5 Using the Data Model

January 03, 2023 - by Bill Jelen

Problem: The Top 5 Report would be easier if this setting were not always greyed out:

On the Design tab in the Ribbon, open the Subtotals drop-down. The last choice, Include Filtered Items in Totals is perpetually greyed out. — Figure 914. Include Filtered Items in Totals is always greyed out when your data comes from a sheet.

Strategy: In Excel 2013 or newer, when you create the pivot table, choose

When creating the pivot table, choose the box for Add This Data To the Data Model. — Figure 915. Copy your data to the “external” data model.

Create the pivot table with the top five customers. Since the data model is considered external to the worksheet, you can select Analyze, Subtotals, Include Filtered Items in Totals. The grand total row will appear with an asterisk and the total of all customers will appear.

WIth the data in the external data model, you can choose Include Filtered Items in Totals. The Grand Total appears with an asterisk, and the report includes the total $6.7 Million. — Figure 916. The Grand Total asterisk means hidden customers are included in the total.

This article is an excerpt from Power Excel With MrExcel

Title photo by Takahiro Sakamoto on Unsplash