Pivot Table Issues

How to Show Sum in Pivot Table Columns and Average in Grand Total

Khadija Khan Sep 30, 2026 868 views

Question details

The user wants to calculate the sum for detail columns in a Pivot Table while displaying the average of those sums in the grand total row.

How to Show Sum in PivotTable Columns and Average in the Grand Total

Product: Spreadsheet
Device & OS: not provided
Scenario: Customizing the calculation method for the Grand Total row to differ from the calculation used in the main value columns of a Pivot Table.
Observed behavior: A standard Pivot Table applies the same aggregate function (e.g., Sum) to both the data fields and the grand total, preventing users from mixing sum and average natively.

Before you start

Ensure your spreadsheet application supports the Data Model and Power Pivot, as standard Pivot Tables do not natively support using different aggregation methods for values and grand totals.

Solution 1Recommended

Use DAX Measures in Power Pivot

Create custom DAX measures to separate the calculation logic for the individual columns and the grand total row.

By leveraging Power Pivot, you can write DAX (Data Analysis Expressions) formulas that conditionally calculate a sum for standard rows or columns, while applying an average when evaluating the grand total context.

Add data to the Data Model

Select your data table, navigate to the Power Pivot tab on the ribbon, and click 'Add to Data Model'.

Create a base Sum measure

In the Power Pivot window, click 'Measures' and select 'New Measure'. Create a base measure to calculate the sum using a formula like: BaseSum = SUM(Table[ColumnName]).

Create the Average Grand Total measure

Create a second measure that iterates over your categories using AVERAGEX. Use a formula similar to: AverageTotal = IF(HASONEVALUE(Table[Category]), [BaseSum], AVERAGEX(VALUES(Table[Category]), [BaseSum])).

Apply the measure to your Pivot Table

Insert a new Pivot Table from the Data Model. Drag your newly created 'AverageTotal' measure into the Values area. The columns will now display the sum, and the Grand Total will display the average.

Power Pivot Requirement: DAX measures require the Power Pivot add-in, which is primarily available in advanced versions of Microsoft Excel and may not be supported in basic spreadsheet tools.

Free Microsoft Office alternative

Need a Lightweight Data Analysis Tool? Try WPS Office

While advanced DAX measures require Microsoft Excel's Power Pivot, WPS Office Spreadsheet provides a fast, intuitive, and highly compatible environment for standard data analysis. You can easily manage traditional Pivot Tables, use helper columns, and apply robust built-in functions to summarize and average your data effortlessly.

Seamlessly compatible with Microsoft Excel (.xlsx, .xls) formatsLightweight architecture for fast data processing without laggingIntuitive UI for quick Pivot Table and chart generationCompletely free to use with rich basic functionality

microsoft office alternative - wps office

Frequently Asked Questions

Can I change the Grand Total calculation without using Power Pivot?

In a standard Pivot Table, the Grand Total inherits the same calculation (like Sum or Count) as the data field. To show a different metric like an average, you generally need Power Pivot, or you must calculate the average manually using formulas outside the Pivot Table area.

How do I change the summary function for a standard Pivot Table value?

Right-click any value cell inside your Pivot Table, hover over 'Summarize Values By', and choose your desired function, such as Sum, Count, or Average. Note that this change applies to both the data columns and the grand total.

Why is my Grand Total showing a sum instead of an average?

Standard Pivot Tables use a single aggregate function for the entire field. If your field is set to 'Sum' to calculate the detail rows, the Grand Total automatically sums those totals rather than averaging them.

How can I display both a Sum and an Average in the same Pivot Table?

You can drag the same data field into the 'Values' area twice. Click on the first one and set it to summarize by 'Sum', then click the second one and set it to summarize by 'Average'. This will create two separate columns for your data rather than combining different logic into the Grand Total.

Excel & Data Problems