Pivot Table Issues

Calculate the Difference Between Two Averages in an Excel PivotTable

Steve KSteve K Nov 18, 2026 870 views

Question details

Calculate the difference between two averages shown in an Excel PivotTable.

Calculate the Difference Between Two Averages 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.
Before you start

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.

Solution 1Recommended

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.

1
Add the data to the model

Select the source data and choose Insert > PivotTable. Select Add this data to the Data Model, then create the PivotTable.

2
Create the first average

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.

3
Create the second average

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.

4
Create the difference measure

Choose Power Pivot > Measures > New Measure, name it Difference, and enter =[Average 2]-[Average 1].

5
Show the results

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.

Subtract two average measures with Power Pivot
Power Pivot availability: Power Pivot and Data Model features depend on your Excel version and platform. If Power Pivot is unavailable, these DAX measure steps may not be supported.
Free Microsoft Office alternative

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.

Create PivotTables to summarize and compare spreadsheet data.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for an easier transition.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.