Prices that multiplied the sales
Northwind's Product query loads from SQL Server and then merges with an Excel price list on SKU to bring in ListPrice. The price list holds one row per SKU per price change - about 2,400 rows for 900 SKUs - joined as a left outer join with all columns expanded, and nothing is removed afterwards. The model contains Product[SKU] and Sales[SKU]. Power BI created the Sales-to-Product relationship as many-to-many and refresh throws no error. The matrix of Sales Amount by Product Name has a grand total of 10.0M, matching the source, but adding up the individual product rows gives 11.8M; only SKUs with more than one price-list row are affected. A card counting distinct customers is unchanged.
1. Explain why the product rows sum to 11.8M while the grand total shows 10.0M, and why the customer card is unaffected.
Read the worked answer
The merge changed the grain of Product: a SKU with three price rows becomes three Product rows. Sales[SKU] is still unique in Sales, but Product[SKU] is not, so Power BI cannot build a one-to-many relationship and falls back to many-to-many. It shows the cardinality but does not treat a non-unique key as an error, and the refresh succeeds.
When a Product column is on rows, each Sales row matches every Product row carrying that SKU, so the amount is counted once per duplicate - the row total inflates. The grand total row is evaluated with no filter on Product, so the fact is not multiplied and the number stays at the source value. The visual is not wrong in the total; it is internally inconsistent, which is the classic signature of a dimension that lost its unique key.
The customer card survives because DISTINCTCOUNT collapses the repeated values. COUNTROWS(Sales) or SUM(Sales[Qty]) by product would have inflated, and either is a fast confirmation. So is computing the same amount from a copy of the fact unrelated to Product.
2. Give the correction in Power Query, and say which 'obvious' fixes are wrong.
Read the worked answer
Fix the grain before the model sees it. First decide what the price list means:
- Current price only (the usual case): sort by SKU ascending and EffectiveDate descending, then
Table.Distinct(..., {"SKU"})so the newest row survives. Product stays at one row per SKU and the relationship returns to*:1. - Historical prices matter for margin at time of sale: do not merge at all. Load the price list as its own table with SKU and EffectiveDate and resolve the price in DAX or through an effective-dated relationship. Flattening a slowly changing dimension into the product row is the real mistake.
Wrong fixes:
Table.Distinctover all columns deletes nothing, because the rows differ in price and date.Table.Distincton SKU without sorting first keeps an arbitrary row, so ListPrice can change between refreshes for no visible reason.- Deleting the relationship but keeping the duplicate rows leaves the wrong cardinality, and the next relationship on SKU fails the same way.
Afterwards confirm in the model that the relationship reads *:1, single direction, with assume referential integrity on.
3. What would you add so this cannot ship again?
Read the worked answer
Three cheap controls, in order of value:
- An assertion inside the query. After the merge, add a step that fails the refresh if any SKU repeats:
if Table.RowCount(Table.Distinct(Product, {"SKU"})) <> Table.RowCount(Product)
then error "Product[SKU] is not unique"
else Product
A failed refresh is visible; a doubled revenue figure is not.
2. A reconciliation measure, for example Variance = [Sales Amount] - CALCULATE([Sales Amount], REMOVEFILTERS(Product)), shown on a data-quality page next to row counts per dimension. If it is ever non-zero, a dimension has fanned out.
3. A release gate. Any new merge into a dimension gets a key-uniqueness check and a cardinality review on the model diagram before publishing, and the grain of each dimension is written down next to it.
Also worth noting: this defect is invisible in any visual that does not group by a Product column, which is why it survived to release.