Pivot Table Issues

How to Average Five Ranking Values in an Excel Power Pivot Measure

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Calculate the average of five ranking values in a Power Pivot PivotTable using a DAX measure.

How to Average Five Ranking Values in an Excel Power Pivot 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.
Before you start

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.

Solution 1Recommended

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.

1
Add the source to the Data Model

Select a cell in the source data, choose Insert > PivotTable, and select Add this data to the Data Model before creating the PivotTable.

2
Create the measure

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.

3
Add and check the result

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.

Create a DAX measure for five equally weighted ranks
Check totals separately: A total is recalculated in its own filter context. If it contains ranks for multiple items, dividing their combined sum by five may not produce the average you expect.
Free Microsoft Office alternative

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.

Use spreadsheet formulas to analyze ranking dataOpen and edit Microsoft Excel XLSX and XLS filesNavigate a familiar spreadsheet interfaceMove everyday spreadsheet workflows to a lightweight office suite
QA img-9

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.