How to Average Five Ranking Values in an Excel Power Pivot Measure
Question details
Calculate the average of five ranking values in a Power Pivot PivotTable using a DAX measure.

- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user wants an equally weighted average of five ranking values in a PivotTable based on the Excel Data Model.
- Observed behavior
- The desired result is an average calculated from the ranking values rather than from displayed Show Values As percentages or rankings.
Check that the five ranks are available as numeric values in your source data and that each PivotTable context contains the intended five ranks. The examples below assume the ranks are stored in a column named Rank in a table named Rankings.
Create a DAX measure for five equally weighted ranks
Use this method when each PivotTable calculation context contains exactly five valid ranking values.
The measure sums the underlying Rank column and divides the result by five. It does not average rankings or percentages generated by the PivotTable's Show Values As setting.
Select a cell in the source data, choose Insert > PivotTable, and select Add this data to the Data Model before creating the PivotTable.
Select the PivotTable, then choose Power Pivot > Measures > New Measure. Select the Rankings table, name the measure Average Rank, and enter =DIVIDE(SUM('Rankings'[Rank]), 5) in the formula box. Replace the table and column names with your own.
Drag Average Rank from the PivotTable Fields list into Values. Check a row with known ranks and verify that the source data and filters provide exactly five values for that row.

Average the available ranks when the count varies
Use this method when some ranks are missing and the average should include only numeric rank values present in the current context.
Work with ranking data in WPS Spreadsheets
For everyday spreadsheet work, WPS Office is a free, lightweight alternative with a familiar interface and support for Excel formats, making it easy to open and work with existing files. Power Pivot DAX measures are a Microsoft Excel feature and should be maintained in Excel.

Frequently Asked Questions
Why divide the total rank by five?
Dividing by five gives an equally weighted average only when exactly five valid ranking values are included in the current PivotTable context. If the count varies, use COUNT on the numeric Rank column as the divisor.
Can I average the ranks shown by Show Values As?
A measure should use the underlying rank values or ranking measures, not assume that PivotTable display calculations are stored as source values. Check how the displayed rankings are produced before creating the DAX measure.
Why does the grand total look different from the row averages?
Excel evaluates a measure again in the grand total's filter context. If that context contains more than five rank values, SUM divided by five may not represent the average of the visible row results.
What if my five ranks are separate measures instead of rows in a Rank column?
Create a measure that adds the five existing measures and divides by five, for example =DIVIDE([Rank 1]+[Rank 2]+[Rank 3]+[Rank 4]+[Rank 5], 5). Use this only when all five should contribute in the current context; adjust the divisor if missing measures should be excluded.




