Pivot Table Issues

How to Calculate PivotTable Averages Using Two Criteria in Excel

Emma Brown Sep 27, 2026 868 views

Question details

The user needs to calculate an average based on two criteria within a PivotTable, which first requires obtaining a distinct count of the subjects by school and school level.

How to Calculate PivotTable Averages Using Two Criteria in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Averaging the number of subjects offered by school level and subject grouping.
Observed behavior: A standard Excel PivotTable does not directly support calculating an average based on a distinct count of multiple criteria without advanced modeling.

Before you start

Ensure your version of Excel supports the Data Model and Power Pivot features, as standard PivotTables cannot perform this nested calculation directly.

Solution 1Recommended

Use the Data Model and Power Pivot to Calculate Averages

By adding your data to the Excel Data Model, you can create a custom measure to perform a distinct count before averaging the results.

A standard PivotTable summarizes raw data directly and cannot evaluate a summarized result (like a distinct count) again to find an average. Integrating Power Pivot allows you to write Data Analysis Expressions (DAX) to bridge this gap.

Add data to the Data Model

Select your source data range. Go to the 'Insert' tab, click 'PivotTable', and check the box that says 'Add this data to the Data Model' before clicking OK.

Create a distinct count measure

In the PivotTable Fields pane, right-click your table name and select 'Add Measure'. Write a DAX formula to calculate the distinct count of subjects offered by school and school level.

Calculate the average

Add your newly created measure to the 'Values' area of the PivotTable. Change the Value Field Settings of this measure to 'Average' to get the final required calculation.

Use the Data Model and Power Pivot to Calculate Averages

DAX Formula Requirement: You will need a basic understanding of DAX syntax to create the distinct count measure properly within the Power Pivot window.

Free Microsoft Office alternative

Simplify Data Analysis with WPS Spreadsheet

While advanced Data Model features like Power Pivot are specific to Microsoft Excel, WPS Spreadsheet offers robust standard PivotTables, powerful multi-criteria formulas like AVERAGEIFS, and seamless compatibility with Excel formats—completely free of charge.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your existing .xlsx workbook containing the school and subject data.
2. Use helper columns for distinct counting: Instead of complex Data Models, add a helper column to your raw data using standard COUNTIFS formulas to identify distinct subjects per school.
3. Insert a PivotTable: Select your expanded dataset, go to the 'Insert' tab, and click 'PivotTable'. Use your helper column in the 'Values' field set to 'Average' to achieve your goal simply.

Highly compatible with Microsoft Excel (.xlsx) files and standard PivotTablesLightweight application that runs smoothly on Windows, Mac, and LinuxFamiliar user interface for a zero learning curveSupports powerful data analysis formulas like AVERAGEIFS and COUNTIFS for multi-criteria calculations

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I calculate an average of a distinct count in a standard PivotTable?

Standard PivotTables do not support nested aggregations. They evaluate the base data rows directly rather than calculating an average on top of an intermediate grouped result like a distinct count.

What is the Excel Data Model?

The Data Model is an integrated engine in Excel that allows you to combine data from multiple tables, create relationships, and perform complex calculations using DAX (Data Analysis Expressions) formulas.

Are there alternative formulas to calculate averages with two criteria?

Yes, you can use the AVERAGEIFS function in a standard worksheet cell to calculate an average based on multiple criteria without relying on a PivotTable.

Excel & Data Problems