Pivot Table Issues

How to Show the Overall Grand Total Instead of Top 10 Total in Excel Pivot Tables

Elise Williams Oct 10, 2026 868 views

Question details

The user wants to display the grand total of the entire dataset in an Excel Pivot Table, rather than having the total update to reflect only the sum of the filtered Top 10 items.

How to Show the Overall Grand Total Instead of the Top 10 Total in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering a Pivot Table to show only the Top 10 records while still needing to present the total sum of the entire original dataset.
Observed behavior: By default, the Pivot Table Grand Total only calculates the sum of the visible (Top 10) records, omitting the hidden data from the calculation.

Before you start

Ensure your source data is formatted as an official Excel Table (Ctrl+T) and check if your version of Excel supports the Data Model and Power Pivot features, as they are required for calculating unfiltered totals directly inside the Pivot Table.

Solution 1Recommended

Calculate Overall Total Using Power Pivot and a DAX Measure

By adding your data to the Data Model, you can create a custom DAX measure that ignores the Top 10 filter and forces the Pivot Table to calculate the total for the entire underlying dataset.

This method requires using Excel's Data Model. A DAX (Data Analysis Expressions) formula allows you to override the default Pivot Table filtering behavior so the grand total reflects all records.

Insert a Data Model Pivot Table

Select your source data table, go to the 'Insert' tab, click 'PivotTable', and check the box that says 'Add this data to the Data Model' before clicking OK.

Add a New Measure

In the PivotTable Fields pane, right-click the name of your table at the top and select 'Add Measure' (or 'Add DAX Measure').

Write the DAX Formula

In the Measure dialog, create a formula using CALCULATE and ALL. For example: =CALCULATE(SUM([SalesAmount]), ALL(TableName)) and name the measure 'Overall Total'.

Add Measure to the Pivot Table

Drag your newly created 'Overall Total' measure into the Values area of your Pivot Table. This column will now show the total of the entire dataset, even when a Top 10 filter is applied.

Calculate Overall Total Using Power Pivot and a DAX Measure

Filter Bypass Successful: The ALL() function in your DAX measure specifically tells Excel to ignore any active filters on the table, ensuring your grand total is always comprehensive.

Solution 2

Calculate the Overall Total Outside the Pivot Table

If you do not want to use the Data Model or DAX formulas, you can calculate the overall total directly from your source data using standard Excel functions placed outside the Pivot Table area.

Easily Manage Pivot Tables and Totals with WPS Spreadsheets

WPS Spreadsheets provides a highly intuitive and robust environment for handling complex datasets. You can effortlessly create Pivot Tables, apply Top 10 filters, and utilize straightforward spreadsheet formulas to display your overall grand totals clearly.

1. Import Your Data: Open your dataset in WPS Spreadsheets and select the data range you want to analyze.
2. Insert a Pivot Table: Navigate to the 'Insert' tab and click 'PivotTable' to generate your analytical report on a new sheet.
3. Apply Top 10 Filter: Click the filter arrow on your Row Labels, go to 'Value Filters', select 'Top 10...', and click OK.
4. Calculate Overall Total: Use a simple =SUM() formula in a cell outside the Pivot Table referencing your original data tab to clearly display the overall grand total.

Completely free and lightweight spreadsheet softwareSeamless compatibility with Microsoft Excel (.xlsx) filesIntuitive Pivot Table creation and value filteringEasy-to-use formula engine for external total calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Grand Total change when I apply a Top 10 filter in a Pivot Table?

By default, standard Excel Pivot Tables calculate the Grand Total based only on the visible rows within the table. When a Top 10 filter hides the rest of your data, those hidden values are automatically excluded from the default total calculation.

Can I show both the Top 10 Total and the Overall Grand Total inside a standard Pivot Table without Power Pivot?

No, standard Pivot Tables do not natively support displaying an unfiltered overall grand total alongside filtered rows. To achieve this inside the table, you must use the Data Model (Power Pivot). Otherwise, you must calculate the overall total in a regular cell outside the Pivot Table.

What is the Data Model feature in Excel?

The Data Model is an advanced analytical feature in Excel that allows you to integrate data from multiple tables and create sophisticated calculations using DAX (Data Analysis Expressions) formulas. It is required for overriding standard Pivot Table filters to show overall totals.

How do I remove the Top 10 filter to restore my original Pivot Table Grand Total?

To remove the filter, click the small funnel/dropdown icon on your Row Labels header, and select 'Clear Filter From [Field Name]'. The Pivot Table will immediately update to display all records and restore the original overall Grand Total.

WPS Office