How to Calculate Distinct Average Duration in Excel Power Pivot with DAX
Question details
The user wants to create a Power Pivot measure that averages durations by distinct application or ID rather than averaging every row or distinct duration value.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user is building a Power Pivot model and needs a duration average that avoids double-counting rows where the same application or ID appears multiple times.
- Observed behavior
- The calculation should return the average duration per distinct application or ID, using a DAX expression such as AVERAGEX over a distinct summarized table.
Confirm that the Power Pivot add-in is enabled in Excel, and identify the exact table, ID, and duration column names in your data model before writing the DAX measure.
Create an AVERAGEX Measure over a Distinct ID and Duration Table
Use this reliable DAX calculation when you need one duration average per distinct application or ID.
The principle is to build a one-row-per-ID table with SELECTCOLUMNS and DISTINCT, then apply AVERAGEX so duplicate rows for the same ID do not inflate the average.
In Excel, open the Power Pivot tab, click Manage Data Model, and select the calculation area below the table where the measure should be stored.
Type =AVERAGEX(DISTINCT(SELECTCOLUMNS('Table',"ID",'Table'[ID],"Value",'Table'[Value])),[Value]) and replace 'Table', 'ID', and 'Value' with your actual table and column names.
Press Enter to save the measure, then add it to the Power Pivot PivotTable and verify that it averages by distinct ID instead of by every row.
Work Around by Collapsing the Node ID Field in the PivotTable
Use this PivotTable-only workaround when you cannot edit the DAX measure and need a quick average grouping.
Use WPS Office Spreadsheets for Lightweight Pivot Table Averages
Power Pivot DAX measures are a Microsoft Excel-specific feature, but WPS Office gives you a free, lightweight spreadsheet with familiar pivot table tools, Excel .xlsx compatibility, and a simple interface for everyday average and summary tasks.
- 1. Download and install WPS Office: Click Download WPS Office Free from the official WPS website and install WPS Spreadsheets on your device.
- 2. Open the existing Excel workbook: Launch WPS Spreadsheets, click Open, and select your .xlsx workbook; standard data and formulas remain compatible.
- 3. Create a PivotTable for averages: Select the data range, click Insert > PivotTable, then add the ID or application field as rows and the duration field as Values with Average.
Frequently Asked Questions
Can WPS Office open Excel files that contain Power Pivot measures?
WPS Spreadsheets can open .xlsx files, but Power Pivot DAX measures are not supported. The raw data and standard pivot tables remain available.
Why use AVERAGEX instead of AVERAGE for a distinct duration?
AVERAGE summarizes all rows in the current filter context, while AVERAGEX over a DISTINCT SELECTCOLUMNS table creates one row per distinct ID and duration combination before averaging, preventing double-counting.
What does DISTINCT(SELECTCOLUMNS(...)) do in the measure?
SELECTCOLUMNS builds a table containing only the ID and Value columns. DISTINCT then removes duplicate combinations so each distinct ID and duration appears once.
Can this DAX measure be used in a normal Excel PivotTable?
No. A normal Excel PivotTable does not run DAX measures. You must create the measure in the Power Pivot data model and use it in a Power Pivot or Data Model PivotTable.
What should I replace in the example DAX formula?
Replace 'Table' with your actual table name, 'ID' with the application or ID column, and 'Value' with the duration column in your Power Pivot model.
