Display Monthly Averages in Excel Data Model PivotTable Subtotals
Question details
Show monthly sums in a Data Model PivotTable and average those monthly totals in subtotals.

- Product
- Microsoft Excel Data Model PivotTable
- Device & OS
- not provided
- Scenario
- A PivotTable groups values by month and quarter, with quarterly subtotals intended to represent the average of the monthly totals.
- Observed behavior
- Standard value-field options may not calculate the average of monthly totals at the subtotal level.
Confirm that the PivotTable uses the Data Model and that Power Pivot is available in your Excel version. Prepare a nonblank month key that distinguishes both year and month, such as 2025-01.
Create a DAX measure to average monthly totals
Use an explicit measure when monthly rows should show sums and quarter subtotals should average those sums.
The measure sums values for each month, then averages those monthly sums in the current subtotal or total context. Replace the table and column names in the formula with your own.
In Excel, look for the Power Pivot tab. If it is missing, check File > Options > Add-ins, select COM Add-ins, click Go, and enable Microsoft Power Pivot for Excel if it is available.
Choose Power Pivot > Measures > New Measure. Select the table, name the measure Monthly Total or Average, and enter this formula: =IF(HASONEVALUE(tbl[MonthKey]),SUM(tbl[Value]),AVERAGEX(VALUES(tbl[MonthKey]),CALCULATE(SUM(tbl[Value]))))
Click OK, select the PivotTable, and drag Monthly Total or Average from the PivotTable Fields list into Values. Remove the previous value field if it is no longer needed.
Place your year, quarter, and MonthKey fields in Rows, enable quarter subtotals if needed, and compare a quarter subtotal with the average of its displayed monthly sums.

Work with spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Office files. Its format compatibility can make moving everyday spreadsheets straightforward, but it does not fix Excel Data Model or Power Pivot behavior.

Frequently Asked Questions
Why doesn't the PivotTable Average option average monthly totals?
The standard Average aggregation can average underlying values rather than the sums for each month. An explicit DAX measure can first calculate each monthly sum and then average those sums.
Why does the month key need to include the year?
A month name alone can combine the same month across different years. Use a distinct year-month key, such as 2025-01, in the measure and PivotTable rows.
Does the measure count months with no data as zero?
No. It averages month keys present in the filtered source table. Counting missing months as zero requires a complete calendar table and a measure designed for that behavior.
Where can I enable Power Pivot?
In Excel, go to File > Options > Add-ins, select COM Add-ins, and click Go. Enable Microsoft Power Pivot for Excel if it is listed; availability depends on your Excel version.




