Pivot Table Issues

Display Monthly Averages in Excel Data Model PivotTable Subtotals

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Show monthly sums in a Data Model PivotTable and average those monthly totals in subtotals.

Display Monthly Averages in Excel Data Model PivotTable 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.
Before you start

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.

Solution 1Recommended

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.

1
Check Power Pivot

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.

2
Create the measure

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]))))

3
Add it to the PivotTable

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.

4
Check the subtotals

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.

Create a DAX measure to average monthly totals
Months included in the average: The measure averages months present in the filtered table. Months with no rows are not automatically counted as zero, and the grand total averages the monthly totals in its current filter context.
Free Microsoft Office alternative

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.

Open and edit Excel XLSX and XLS spreadsheets.Use spreadsheet tools to summarize and review monthly data.Navigate a familiar interface when moving from Microsoft Office.Use a lightweight office suite for everyday document work.
microsoft office alternative - wps office

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.