Calculate the Difference Between Two Averages in an Excel PivotTable
Question details
Calculate the difference between two averages shown in an Excel PivotTable.

- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- A PivotTable compares the averages of two numeric fields, and the user wants to subtract one average from the other.
- Observed behavior
- A standard calculated field may subtract underlying sums rather than the displayed averages.
Check that the source data contains two numeric columns and that Power Pivot is available in your Excel edition. If the Power Pivot tab is missing in Excel for Windows, check File > Options > Add-ins, then select COM Add-ins under Manage.
Subtract two average measures with Power Pivot
Use DAX measures when a standard PivotTable calculated field does not subtract the displayed averages.
Each average measure is evaluated in the PivotTable's current filter context. The difference measure then subtracts those two results.
Select the source data and choose Insert > PivotTable. Select Add this data to the Data Model, then create the PivotTable.
On the Power Pivot tab, choose Measures > New Measure. Select the source table, name the measure Average 1, and enter =AVERAGE('Data'[Value 1]). Replace Data and Value 1 with your actual table and column names.
Choose Power Pivot > Measures > New Measure again. Name it Average 2 and enter =AVERAGE('Data'[Value 2]), replacing the table and column names with your own.
Choose Power Pivot > Measures > New Measure, name it Difference, and enter =[Average 2]-[Average 1].
In the PivotTable Fields pane, drag Average 1, Average 2, and Difference into Values. Add the desired categories to Rows or Filters to compare results by group.

Explore PivotTable data with WPS Spreadsheets
WPS Office is a free, lightweight alternative for working with spreadsheets in a familiar interface. Open compatible Excel files and continue routine spreadsheet work as you migrate; Excel Power Pivot DAX measures may not be supported.

Frequently Asked Questions
Why does my calculated field not subtract the displayed averages?
A standard PivotTable calculated field works with the source fields rather than directly subtracting the displayed average results. Create separate average measures in Power Pivot, then subtract those measures.
How do I change which average is subtracted?
Edit the Difference measure through Power Pivot > Measures. Use =[Average 1]-[Average 2] to reverse the subtraction order.
Will the difference update when I filter the PivotTable?
Yes. The DAX average measures and their difference are evaluated in the current PivotTable filter context, including applicable row, column, and filter selections.
What if I cannot find the Power Pivot tab?
In Excel for Windows, check File > Options > Add-ins, select COM Add-ins under Manage, and look for Microsoft Power Pivot. Availability depends on your Excel edition and platform.




