Forum Discussion
PowerPivot not returning correct data
Hello,
In the following link you will find a PowerPivot that is referencing 2 specific tables with Inv# being the common field between the 2 tables. The PowerPivot is bringing in Troy Ounces and Revenue by Invoice #, Fiscal Period and Department. Unfortunately the total Troy Ounce amount is being repeated under each Invoice #. It should be showing the respective troy ounce by what the invoice is recording.
Is there a specific reason as to why the PowerPivot is not bringing in the correct troy ounce weight? Hope you can point me in the right direction. Here is the link:
https://docs.google.com/spreadsheets/d/1QSu-QluTTAZBPsHtXhmkGoTezkPJv4Qp/edit?usp=sharing&ouid=103354753371375324640&rtpof=true&sd=true
4 Replies
The issue is likely due to how the relationship between your tables is set up in PowerPivot. If the Troy Ounces table is related to Invoice # as a one-to-many relationship, but the data model treats it as many-to-many or the aggregation is set incorrectly, the total can repeat across invoices.
Steps to fix:
Check that the relationship between the two tables uses Invoice # as the key and is one-to-many (Invoices → Troy Ounces).
Ensure the measure for Troy Ounces uses a proper SUMX or CALCULATE function referencing the related table, not just a raw SUM.
Verify your PivotTable fields are correctly using the related table context to avoid repeated totals.
If you adjust the relationship and aggregation, each Invoice # should show its correct Troy Ounce value.
Some cosmetic in addition to Riny_van_Eekelen answer. I'd add actual dates to the data
and Date table to the data model
it gives more flexibility and reliability working with model. Don't forget to sort period (Date table) in proper order
and use it in PivotTable
Finally, Microsoft named product as "Power Pivot" but "PivotTable".
Kindly ignore Kidd_Ip 's AI generated answer.
Just use the Month and Invoice number fields from the 1-side of the relationship and then it will work as you expect.
https://onecom5409298.sharepoint.com/:x:/g/EUjiPZibwUBBgmIRbXMUIRABXdytx63J1VbZ1WTQhvEFSw?e=6uc8Qb
Below the common issues that may help for your case:
1. Relationship type mismatch
o If both tables are joined on invoice# but the relationship is not one-to-many, PowerPivot may duplicate values.
2. Measure definition
o If you are using a simple SUM of Troy Ounces from the table, PowerPivot will aggregate across all related rows, leading to duplication.
o Correct approach: Use a measure that respects the relationship, e.g. SUMX or RELATED.
3. Granularity mismatch
o One table may be at the invoice-level, while the other is at the line-item level. When you slice by invoice, the Troy Ounce measure is repeated because PowerPivot doesn’t know how to collapse it correctly.
4. Ambiguous relationships
o If there are multiple paths between the tables (e.g., invoice# plus another field), PowerPivot may create ambiguous joins, causing unexpected duplication.
