PowerPivot not returning correct data

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.