How to Calculate the Average of PivotTable Aggregate Values
Question details
The user needs to calculate the average of the visible summarized totals (aggregate values) in a PivotTable rather than the arithmetic mean of the underlying raw data records.
- Product
- Spreadsheet Software
- Device & OS
- not provided
- Scenario
- Working with a PivotTable where standard summary functions calculate the average based on every individual row in the source data, but the goal is to average the grouped row or column subtotals.
- Observed behavior
- Standard PivotTable average functions return the mean of the base records, completely ignoring the visual aggregations displayed in the rows and columns.
Ensure your source data is formatted as a Table to prevent missing data ranges, and verify whether your spreadsheet software supports Data Models or Power Query.
Use a Data Model and DAX Measures (Power Pivot)
Use Power Pivot's Data Analysis Expressions (DAX) to create an iterating measure that calculates averages based on the visible aggregated categories rather than underlying rows.
Standard PivotTable calculations do not iterate over subtotals natively. By adding your data to the Data Model, you can use the AVERAGEX function to force the PivotTable to first sum the data by category, and then calculate the average of those sums.
Select your source data, click Insert > PivotTable, and check the box that says 'Add this data to the Data Model'.
Open the Power Pivot window or use the Measures option to create a new DAX measure that sums your target column (e.g., Total Sales = SUM([Sales])).
Create a second measure using AVERAGEX to iterate over your categories. For example: AVERAGEX(VALUES('Table'[Category]), [Total Sales]).
Place this newly created AVERAGEX measure into the Values area of your PivotTable to display the average of the aggregated values.
Pre-aggregate Data Using Power Query
Group and summarize your data using Power Query before loading it into a PivotTable, allowing standard Average functions to work correctly on the totals.
Use Helper Columns or Extracted Data
Extract the calculated sums from an initial PivotTable to easily average the visible aggregates using standard formulas without advanced add-ins.
Easily Analyze and Summarize Data with WPS Spreadsheet
WPS Spreadsheet offers powerful, intuitive PivotTable functionalities. While highly complex DAX calculations require specialized plugins, you can effortlessly calculate the average of aggregate values in WPS by utilizing multi-tier data grouping, helper tables, and advanced formula extraction.
- 1. Insert a PivotTable: Open your dataset in WPS Spreadsheet, select your data range, and click Insert > PivotTable.
- 2. Configure Row and Value Fields: Drag your main categories into the Rows area and the fields you want to summarize into the Values area.
- 3. Extract and Average: Use the GETPIVOTDATA function in an adjacent cell to reference the aggregated totals, then wrap them in an AVERAGE function to achieve your goal.
Frequently Asked Questions
Why does changing the summary function to Average give me the wrong number?
When you right-click a PivotTable value and change 'Summarize Values By' to 'Average', the software calculates the arithmetic mean of every individual underlying row that makes up that category, not the average of the summarized subtotals you see on the screen.
Can I use a Calculated Field to average PivotTable subtotals?
Standard Calculated Fields in PivotTables operate on the sum of the underlying base data. Because of this structural limitation, a Calculated Field cannot independently iterate through and average visible subtotals without using external data models.
What is the difference between AVERAGE and AVERAGEX?
AVERAGE is a standard function that calculates the mean of a specific column. AVERAGEX is an iterating DAX function used in Data Models that evaluates an expression (like a sum) for every row of a given table or category, and then averages those results.
