Pivot Table Issues

Fix Repeated Totals in an Excel Power Pivot Relationship

Anum MushtaqAnum Mushtaq Nov 22, 2026 868 views

Question details

Determine why an Excel PivotTable shows the same total for every row despite an existing Power Pivot relationship.

Fix Repeated Totals in an Excel Power Pivot Relationship
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A PivotTable uses related tables in the Data Model, and the user expects each row to show its matching value.
Observed behavior
Every row displays the same total, although matching data works with VLOOKUP. Recreating the relationship and testing a new workbook did not resolve the issue.
Before you start

Identify the table and column used for the PivotTable rows, the table containing the values, and any DAX measure used in the Values area. Confirm that both tables are in the same Data Model.

Solution 1Recommended

Check the Relationship and PivotTable Filter Context

Use this when a relationship exists but row labels do not filter the displayed total.

1
Inspect the relationship

In Excel, open Data > Relationships or Power Pivot > Manage > Design > Manage Relationships. Check that the relationship is active and connects the intended columns in the row-label and value tables.

2
Check the matching columns

In Power Pivot > Manage, inspect both relationship columns. Make sure their data types match, the lookup-side column has unique values, and matching keys do not differ because of extra spaces or other formatting.

3
Verify the row and value fields

In the PivotTable Fields pane, check that the Rows field comes from the lookup-side table and the Values field comes from the related data table. A VLOOKUP match alone does not confirm that the PivotTable filter is reaching the value table.

4
Review the measure

In Power Pivot > Manage, select the value table and inspect the measure in the calculation area. Check for DAX functions such as ALL or REMOVEFILTERS that may remove the row filter, and compare the result with a simple sum of the value column.

5
Refresh and test

Select Data > Refresh All, then check whether different row labels show different results. If needed, create a new PivotTable from the workbook's Data Model and test the same fields.

Check the Relationship and PivotTable Filter Context
Relationship already recreated: Recreating the relationship and testing a new workbook have already been tried in this case. Focus on the matching keys, field selection, and measure before repeating those steps.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easier transition. It does not repair Excel Power Pivot relationships; check complex Data Model features before migrating a workbook.

Open and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface for everyday analysisWork with PivotTables in supported spreadsheetsGet a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does every PivotTable row show the grand total?

The row field may not be filtering the value table, or a DAX measure may be removing that filter. Check Data > Relationships, the fields in the PivotTable Fields pane, and the measure in Power Pivot.

Why does VLOOKUP work when the Power Pivot relationship does not?

VLOOKUP matching does not prove that a Data Model relationship is active or that its filter reaches the value table. Compare the relationship columns, their data types, and the tables used in the PivotTable.

What should I check in the relationship columns?

Open Power Pivot > Manage and compare the key columns. They should have matching data types, and the lookup-side column must contain unique values. Check for extra spaces and unmatched keys.

Can a DAX measure cause the same value to appear on every row?

Yes. A measure using ALL or REMOVEFILTERS can remove the row filter. Inspect the measure in the Power Pivot calculation area and compare it with a simple sum.

What if recreating the relationship does not help?

Review the keys, PivotTable fields, and DAX measure first. Then update Excel and, on Windows, try repairing Office through Settings > Apps. If the issue persists, share a sanitized workbook with support for investigation.