Fix an Excel Power Pivot Relationship That Miscalculates Costs by Product
Question details
The user needs to troubleshoot a Data Model relationship so costs calculate correctly when summarized by product or name.

- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- A PivotTable uses product information to summarize costs from related Costs and Orders tables in the Excel Data Model.
- Observed behavior
- The relationship may not filter or calculate costs correctly by product or name.
Identify the product key columns in Costs and Orders and the source of the product or name field used in the PivotTable. Check whether a separate Products lookup table is available and whether its product key is unique.
Check product keys and rebuild the Data Model relationships
Use a unique Products lookup table to filter both Costs and Orders, then refresh the model and PivotTable.
In Excel, select Power Pivot > Manage, then open Diagram View. Identify the product key in Costs, Orders, and the Products lookup table.
In the source tables, remove leading and trailing spaces, standardize capitalization, and make the product key data types consistent. Confirm that every product key in Costs and Orders exists in Products.
In the Products source table, check that each product key appears only once. If needed, create a distinct product list using Data > Remove Duplicates; repeated keys can remain in Costs and Orders.
In Power Pivot Diagram View, connect Costs[Product] to Products[Product] and Orders[Product] to Products[Product], with Products on the unique lookup side. If an existing relationship is incorrect, use Data > Relationships to delete it and create the correct relationship.
Select Data > Refresh All. In the PivotTable Fields pane, place the product or name field from Products in Rows and the cost field from Costs in Values, then check the totals against the source data.

Review and summarize spreadsheet data with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move everyday spreadsheet work between applications. It does not repair Excel Power Pivot relationships.

Frequently Asked Questions
Why does the same cost total appear for every product?
The product field may not have an active filter path to Costs. Check Power Pivot > Manage > Diagram View and use the product field from a lookup table related to Costs.
Can the product key appear more than once in Costs or Orders?
Yes. The product key can repeat in Costs and Orders, but it must be unique in the Products lookup column on the one side of each relationship.
What happens if a product in Costs is missing from the lookup table?
The lookup table cannot match that product key, so its costs may not appear under the expected product. Add the missing key to Products, then select Data > Refresh All.
Where can I remove an incorrect relationship?
In Excel, select Data > Relationships, choose the incorrect relationship, and delete it. Then create a relationship using the matching product key and the unique Products lookup column.




