The 8% That Isn't in the File
You inherit a packaged workbook that reads an Excel file with three sheets. Orders (48,120 rows: Order ID, Order Date, Customer ID, Product, Sales) is the only fact table. Customers (12,400 rows: Customer ID, Region, Segment) contains 214 Customer IDs that appear twice - a legacy migration left the same ID with two region spellings. Targets (40 rows: Region, Month, Target) is a monthly plan. The Overview sheet reports $14.2M in Sales; the source team's own pivot on the same file says $13.1M. Region is blank on 3.1% of Sales. Target attainment shows above 100% for every region, which nobody believes. The workbook currently joins Orders to Customers physically in the data source page, left outer on Customer ID, and joins Targets on Region.
1. Where does the ~8% inflation come from, and what is the smallest change that fixes it without editing the Excel file?
Read the worked answer
Diagnosis: the physical join is fanning out. Orders is one row per line, but Customers is not one row per Customer ID in this file - 214 IDs have two rows. A left join emits one output row per matching customer row, so every order line belonging to those 214 customers is duplicated. $14.2M / $13.1M ≈ 1.084: an 8% inflation is exactly the order of magnitude you'd expect if those duplicated IDs are high-value accounts.
Smallest fix: stop joining physically. Put both tables on the logical layer and relate Orders to Customers on Customer ID. Tableau keeps related tables as separate logical tables and aggregates each at its own level of detail, so a repeated customer row cannot multiply Sales.
Verify: COUNTD([Order ID]) before and after must both be 48,120, and SUM([Sales]) must land on $13.1M. If the count still drifts, the duplication is in Orders itself, not Customers.
Do not "fix" this with MIN/AVG tricks or a dedup step buried in a custom SQL join - it hides the defect and breaks the Monday it refreshes.
2. How do you explain the 3.1% of Sales with a blank Region, and how do you prove it is a different problem from the duplication?
Read the worked answer
Two causes are plausible and both are cheap to test:
- Orphan customer IDs - orders whose Customer ID has no row in Customers. A left join keeps them and leaves Region null.
- Genuinely null Region values inside Customers.
Test with ISNULL([Region]) combined with COUNTD([Customer ID]), then look up a sample of those IDs in the Customers sheet. If they simply aren't there, it's case 1.
This is a different mechanism from the fan-out: fan-out adds rows, orphans add nulls. They can coexist - which is why you fix them separately and re-check both totalling $13.1M and the blank share.
Decision you actually have to make: once the tables are related, the relationship's referential integrity setting controls the behaviour. All Values keeps orphan orders (Region shows as null, revenue still counts); Some Values drops them. Choose deliberately, and if finance insists orphan revenue counts, surface an "Unmatched customer" bucket as a visible member rather than letting it fall into a blank header nobody owns.
3. Why is every region beating its target, and how should Targets be modelled?
Read the worked answer
Targets is one row per Region per Month - a coarser grain than an order line. Joining it on Region alone stamps the same monthly target onto every order line in that region, so SUM([Target]) counts the target once per row instead of once per region-month. Attainment above 100% everywhere is the classic signature of that fan-out on the denominator side.
Model it as its own logical table related at the correct grain - Region and the order month (e.g. DATETRUNC('month',[Order Date])) - so Tableau rolls Sales up to Region+Month before comparing. Then compute attainment as an aggregate calculation SUM([Sales]) / SUM([Target]) and sanity-check one region-month against Excel.
If a physical join is unavoidable, aggregate Targets to Region+Month in a subquery first so the join is one-to-one. Whatever you choose, put both numbers on the same sheet for one region so the reader can see the numerator and denominator that produced the ratio.