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
2 Replies
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.
