Pivot Table Issues

Fix an Excel Power Pivot Relationship That Miscalculates Costs by Product

Amos GikundaAmos Gikunda Nov 18, 2026 870 views

Question details

The user needs to troubleshoot a Data Model relationship so costs calculate correctly when summarized by product or name.

Fix an Excel Power Pivot Relationship That Miscalculates Costs by Product
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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the model

In Excel, select Power Pivot > Manage, then open Diagram View. Identify the product key in Costs, Orders, and the Products lookup table.

2
Clean and match the keys

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.

3
Check lookup uniqueness

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.

4
Recreate the relationships

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.

5
Refresh and test

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.

Check product keys and rebuild the Data Model relationships
Use the lookup table for grouping: A product field from Orders may not filter Costs as intended. Using the shared Products lookup table provides a filter path to both tables.
Free Microsoft Office alternative

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.

Review product and cost data in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar interface for everyday spreadsheet tasksMove common Office documents between applications with ease
microsoft office alternative - wps office

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.