The Data Model: The Board Pack Beat Target by £3,900.75 and Every Region In It Missed by More Than £28,000

The half-year pack had one pivot table on it. Rows: Region. Values: Sum of Amount and Sum of Target, with a variance column beside them.

The bottom line read £63,900.75 of sales against a £60,000.00 target — £3,900.75 ahead, 106.50% of plan. That figure was correct. It is still correct today.

The three lines above it read:

Every region missed. The company beat. Both statements came out of the same pivot table, in the same refresh, and neither cell errored.

What happened is that somebody ticked Add this data to the Data Model on three tables — Orders, Customers and Targets — and drew a relationship from Orders to Customers, which is the join the sales figure needs. Targets was never joined to anything. It sat in the model as an island. And a measure on an island does not fail: it returns the value it has when no filter reaches it, which is the sum of the whole table, once per row, forever.

The real answer is that North was £194.60 ahead, South was £698.85 behind, and East was £4,405.00 ahead.

What this covers. Excel's Data Model — the engine behind Power Pivot — ships with Excel 2013 and later on Windows, in every edition from Excel 2013 Standard onward for the model itself; the Power Pivot window is available in Microsoft 365, Office 2019/2021/2024 and the Professional Plus editions of 2013/2016. Relationships and DISTINCTCOUNT work through PivotTable ▸ Add this data to the Data Model even where the Power Pivot ribbon tab is missing; writing your own measures, in section 7, is the part that wants the tab. The worksheet formulas in section 9 — SUMIFS, COUNTIFS, SUMPRODUCT, INDEX/MATCH, IFERROR — work everywhere; XLOOKUP, UNIQUE and SORT need Microsoft 365 or Excel 2021. Sales against target is only the example: actuals against budget, headcount against establishment, tickets against SLA and stock against reorder level are the same two-fact-tables-and-one-dimension shape, and every trap below applies unchanged.

1) The Report, and the Total That Was Right

Three tables. The first is on the grid below and in A1:F15 of your own sheet:

Orders — fourteen rows, January to May 2026. Amount is Units × Unit Price, and there is no region column in it. The orders identify a customer, and nothing else.

Customers — six rows, one per customer, and the only place the word North is ever written down:

Targets — six rows, a quarterly number per region:

Four numbers to hold on to, because everything below is measured against them:

=SUM(F2:F15)                    → 63,900.75   all sales, six customers, five months
=SUMPRODUCT(D2:D15,E2:E15)      → 63,900.75   the same total from units and prices
=SUM(Targets[Target])           → 60,000.00   the whole target table
=COUNTA(Customers[Customer ID]) → 6           customers, and therefore six ways to reach a region

Sales by region, which the pivot got right: North £15,694.60, South £16,801.15, East £31,405.00. Target by region, which the pivot never showed at all: North £15,500.00, South £17,500.00, East £27,000.00.

🎯 Scenario: Before reading a pivot built on the Data Model, put =SUM(Targets[Target]) in a cell on the sheet and compare it with a single line of the pivot's target column. If one region's target equals the whole table, you are looking at an island, and no other number in that column means anything. It is one cell, it takes ten seconds, and it is the only check in this article that needs no menus.

2) The Data Model Is Not Your Worksheets

The first thing to be clear about is what ticking that box did. Add this data to the Data Model does not point a pivot table at your sheet. It loads a copy of the table into an in-memory columnar database that lives inside the workbook, and the pivot reads that copy.

Three consequences follow immediately, and all three surprise people:

• The model is stale until you refresh it. Editing F7 on the sheet changes nothing in the pivot until Data ▸ Refresh All. A worksheet pivot has the same behaviour, but a worksheet pivot at least reads the cells you edited. The model is reading a second copy of them.
• Only tables go in. A range is not eligible; Excel converts it to a Table (Ctrl + T) first, or refuses. That is a feature — a table has a name and named columns, and relationships are drawn between named columns.
• The relationship is stored in the model, not in the formulas. Nothing on the grid records that Orders joins to Customers. There is no VLOOKUP to read, no helper column to inspect, no cell you can click on to see the join. The join is a line in a diagram, and if nobody opens the diagram, the join is invisible.

That last point is the whole article. A worksheet report states its joins in cells, where they are ugly and obvious. A model report states its joins in a diagram nobody opens.

🎯 Scenario: Open Data ▸ Manage Data Model (or the Power Pivot tab if you have it) and switch to Diagram View before you read a single figure from a model-backed pivot. Count the tables, then count the lines. Any table with no line touching it is an island, and every measure from it will repeat.

3) Filters Travel Along Relationships, and There Was No Relationship

A pivot table does not compute a region's number by looking at region rows. It computes it by filtering the model to that region and evaluating the measure. The North row means: set Customers[Region] = "North", then evaluate SUM of Amount and SUM of Target.

The filter on Customers[Region] reaches Orders because there is a relationship from Orders[Customer ID] to Customers[Customer ID], and filters flow from the one side of a relationship to the many side. Two customers are North, so five of the fourteen orders survive the filter and SUM(Orders[Amount]) returns £15,694.60. Correct.

The same filter reaches Targets not at all. There is no path. So SUM(Targets[Target]) is evaluated over an entirely unfiltered table and returns £60,000.00 — the same £60,000.00 it will return on every row of every pivot for the rest of the workbook's life.

This is worth stating plainly, because it is the mechanism behind almost every wrong number a Data Model produces:

An unrelated table does not error. It ignores you. A measure over a table that no filter can reach returns its grand total, correctly and repeatedly, in every cell you put it in.

There is no error state available. #REF! needs a destroyed reference; nothing was destroyed. #N/A needs a failed lookup; nothing was looked up. A blank would at least be visible; the model has no reason to produce one, because SUM over six populated rows is a perfectly good number. The report is wrong in the only way a report can be wrong without anybody noticing: fluently.

🎯 Scenario: The signature of a missing relationship is a column of identical numbers beside a varying row field. Not approximately identical — byte-identical, down the whole column, including subtotals. If you see that once in your working life, do not reach for the number format. Open Diagram View.

4) Why the Grand Total Was Right, and Why That Is the Worst Part

The total row is not the sum of the lines. It never was, in any pivot table, related or not. The total row is the same measure evaluated with the row filter removed.

On a correctly joined model those two things coincide for an additive measure, which is why nobody ever notices the distinction. Here they come apart, and the arithmetic is exact:

Line variances:  −44,305.40  +  −43,198.85  +  −28,595.00  =  −116,099.25
Total row:       63,900.75 − 60,000.00                      =    +3,900.75
Difference:                                                      120,000.00

£120,000.00 is £60,000.00 twice. The target was counted three times across three region lines when the report only owns one copy of it, so the lines carry two extra targets and the total row carries none. The gap between the lines and the total is not noise, and it is not rounding. It is exactly (number of rows − 1) × the whole table, and if a fourth region existed it would have been £180,000.00.

And the percentage column reconciled as well, which is why the pack was signed off. The pivot's achievement figures were East 52.34%, North 26.16%, South 28.00% — every one of them a region's sales divided by the entire company target. They sum to 106.50%, which is exactly the correct company figure, because dividing each part by one constant and adding is the same as adding and then dividing.

The two right-hand columns share a total and agree on nothing else. A check that the parts add to the whole passes here — the parts do add to the whole. They are simply the wrong parts.

🎯 Scenario: On any pivot with a variance or percentage column, put =SUM() over the visible line values in a spare cell and compare it with the total row. For an additive measure on a correctly joined model the two agree. When they do not, the difference is usually a whole table, and dividing it by the number of lines minus one tells you which one.

5) The Fix Is One Table With Three Rows

Targets cannot be joined to Customers, and the reason is the rule that governs every relationship in the model: the one side must be unique. Customers[Region] contains North, North, South, South, East, East. Excel will refuse the relationship, correctly, and the refusal is the point at which most people give up and leave the island where it is.

What both tables need is a third table whose region list is unique — a dimension. It has three rows:

Build it however you like. On Microsoft 365, one formula:

=SORT(UNIQUE(Customers[Region]))      → East, North, South

In Power Query, reference the Customers query, keep the Region column, Remove Duplicates, and load it as a connection to the model — which has the advantage that a new region added to Customers appears here on the next refresh instead of the next time somebody remembers. On any version, typing three words into a table and pressing Ctrl + T is a legitimate answer.

Then draw two relationships from the dimension:

Regions[Region]  1 ──── *  Customers[Region]
Regions[Region]  1 ──── *  Targets[Region]

And — this is the step people skip — put Regions[Region] on the pivot's rows, not Customers[Region]. The dimension is now the thing that filters both fact tables. Filtering Customers[Region] still reaches Orders and still does not reach Targets, because filters travel from one to many and Customers is on the many side of the new relationship. Same pivot, same data, same three lines, and the target column finally moves:

The lines now add to the total: 4,405.00 + 194.60 − 698.85 = 3,900.75. That is what a joined model looks like, and it is the only version of this table where the additivity check means anything.

This shape — one dimension in the middle, fact tables hanging off it — is a star schema, and it is not academic tidiness. It is the arrangement in which a single filter can reach every table that has to answer to it.

🎯 Scenario: Any time two tables share a column name and you find yourself wanting to slice both by it, the answer is a dimension table holding the distinct values of that column, not a relationship between the two facts. Excel will let you try the direct join and will tell you no; the useful response to that refusal is a three-row table, not a helper column.

6) The Rules Excel Enforces, and the Four It Does Not

Excel checks four things when you draw a relationship, and refuses if any fails:

1. The one side is unique. Duplicate keys in the lookup table are rejected outright.
2. The one side has no blanks. A blank key is not a valid identity.
3. The two columns are the same data type. "1001" as text and 1001 as a number are different columns to the model, and it will not coerce them for you the way VLOOKUP sometimes appears to.
4. The relationship creates no ambiguous loop. If two paths would join the same tables, the second is created inactive — a dotted line in Diagram View — and it does nothing until a measure calls USERELATIONSHIP to switch it on.

And here are four it does not check, each of which produces a wrong number rather than a message:

• Values that do not match. A relationship between Orders[Customer ID] and Customers[Customer ID] is happily created when none of the fourteen order keys exist in Customers. Every order then falls into a (blank) row in the pivot, which reads like a formatting oddity and is in fact the report telling you the join matched nothing.
• Trailing spaces. "C-101 " and "C-101" are different keys. The relationship is legal; the match is not made; the rows go to (blank). TRIM in Power Query on both sides, before load, is the only reliable fix.
• A partial match. Five of six customers matching is not an error, it is a smaller number. The sixth customer's orders — potentially your largest, as C-106's £17,460.00 would be here — sit in (blank) while the total stays correct.
• Whether the relationship is the one you meant. A model with a date column in two tables invites a join between them. Excel will make it. It will be wrong and it will be silent.

🎯 Scenario: After every new relationship, drag the key from the many side into a pivot's rows with COUNTROWS of the fact table beside it, and look for a (blank) row. There should not be one. If there is, its size tells you how much of the report is quietly unmatched — and that row survives every total, because the total does not care which line the rows came from.

7) Implicit Measures Are Not Measures

Drag a numeric field into the Values area and Excel writes you an implicit measure called Sum of Amount. It works, it is where everybody starts, and it has three limitations that matter as soon as the report grows past one column:

• It cannot be referenced by another measure. There is no way to write "variance" in terms of it.
• It cannot be reused in a second pivot, or renamed once and everywhere.
• It changes its own meaning if somebody switches the aggregation from Sum to Count in one pivot and not another.

An explicit measure is one you write yourself, once, in the model, and every pivot uses the same definition:

Total Sales      := SUM(Orders[Amount])
Target           := SUM(Targets[Target])
Variance         := [Total Sales] - [Target]
Achievement      := DIVIDE([Total Sales], [Target])
Orders Count     := COUNTROWS(Orders)
Customers Billed := DISTINCTCOUNT(Orders[Customer ID])

Two details in that block earn their keep:

DIVIDE instead of /. [Total Sales] / [Target] returns #DIV/0! on any row where the target is blank or zero — and blanks are common the moment a region exists in one table and not the other. DIVIDE returns BLANK() instead, and a blank cell in a pivot disappears rather than shouting. If you want a specific fallback, DIVIDE([Total Sales], [Target], 0) gives you one.

Measures are written in DAX, and DAX is not localised. The formula language in the Power Pivot window uses English function names in every language build of Excel, and separates arguments with commas, even in a workbook whose worksheet formulas use semicolons and SUMAR.SI.CONJUNTO. This is a genuine difference from the grid, and it catches people out exactly once.

🎯 Scenario: The moment you need a second column derived from a first — a variance, a share, a percentage of plan — stop dragging fields and write the measure. Two explicit measures and one that references them is less work than three implicit ones, and it is the only version where the definition lives in a single place.

8) Counting Things: COUNT, COUNTROWS and DISTINCTCOUNT

The Data Model's one genuinely irreplaceable feature is DISTINCTCOUNT, and it is worth knowing precisely what the three counting options answer on this data:

COUNTROWS(Orders)                    → 14   order lines
COUNT(Orders[Amount])                → 14   numeric amounts, which happens to be the same
DISTINCTCOUNT(Orders[Customer ID])   →  6   customers who bought anything
DISTINCTCOUNT(Customers[Region])     →  3   regions on the customer list

COUNTROWS counts rows. COUNT counts the non-blank values in one named column, so it agrees with COUNTROWS only while that column is full — point it at a column with gaps and the two answers separate. DISTINCTCOUNT is the one a worksheet pivot table cannot do at all, and the one that does not add up: customers billed in North is 2, in South is 2, in East is 2, and the company total is 6 only because no customer spans two regions. Change that assumption and the total stops being the sum of the lines — correctly, this time.

A related trap worth naming: if your customer key were numeric — 101, 102 — dragging it into Values gives you Sum of Customer ID, a number with no meaning that looks like money. Keys that are numbers are a small daily tax; keys that are text are not.

🎯 Scenario: Put Orders Count and Customers Billed side by side in every model-backed pivot you build, at least while you are building it. Two counts that move together confirm the join is filtering; a count that never changes is the same island signature as a repeated total, in a column where it is much easier to spot.

9) The Same Report on the Grid, and Why It Cannot Fail This Way

The worksheet version needs the region on every order line, which means saying the join out loud:

=XLOOKUP($C2,Customers[Customer ID],Customers[Region],"(no customer)")

or, in any version of Excel:

=IFERROR(INDEX(Customers[Region],MATCH($C2,Customers[Customer ID],0)),"(no customer)")

With that column filled down, the report is two SUMIFS and a subtraction, with the region name in $H2:

=SUMIFS(Orders[Amount],Orders[Region],$H2)     → 15,694.60 for North
=SUMIFS(Targets[Target],Targets[Region],$H2)   → 15,500.00 for North
=SUMIFS(Orders[Amount],Orders[Region],$H2)-SUMIFS(Targets[Target],Targets[Region],$H2)

And the checks that keep it honest:

=COUNTIF(Orders[Region],"(no customer)")           → 0   every order found its customer
=SUM(H2:H4)-SUM(Targets[Target])                   → 0   every target row landed on a region
=COUNTIFS(Targets[Region],$H2,Targets[Quarter],"Q1") → 1   one target row per region per quarter

The third one is the check people skip and then need. A duplicated target row does not error, does not change the shape of the report, and quietly doubles one region's plan — the sheet-side version of exactly the counting problem section 4 describes.

This version cannot produce the board pack's error, and it is important to be accurate about why. It is not that SUMIFS is safer than a Data Model — for anything above a few tens of thousands of rows the model is faster, smaller, and better at exactly this job. It is that the worksheet makes you write the join into a cell. SUMIFS(Targets[Target],Targets[Region],$H2) names the column it filters. There is no version of that formula that quietly ignores $H2 and returns the whole table; if the criteria range were wrong you would get 0 or #VALUE!, both of which are visible.

The Data Model's power is that joins are declared once, centrally, instead of repeated in every formula. The cost is that a join which was never declared looks exactly like a join that works.

🎯 Scenario: When you migrate a working SUMIFS report into the Data Model, keep the old sheet alive for one cycle and put =pivot_cell - old_cell between them. Three zeros are worth more than any amount of confidence about how relationships behave, and the reconciliation is the thing you delete last, not first.

10) What the Joined Model Actually Says

The corrected pivot is worth reading, because the number the broken version bragged about was hiding something real. Split the same £63,900.75 by quarter:

The half-year is 106.50% of plan. Q1 was 135.11% and Q2 was 75.92%, and every region fell in the second quarter — East hardest, from 164.89% to exactly 64.00%. A pack that reports +£3,900.75 and stops is not wrong about the £3,900.75. It is simply not the sentence anybody needed.

That split is available only because Targets carries a Quarter column and the joined model can filter on it. The broken version could not have shown this table at all: with no relationship, every cell in it would have read £60,000.00.

🎯 Scenario: When a total beats plan, put the same measure against the next dimension down — quarter, month, channel — before writing the commentary. A single positive number is the least informative shape a model can produce, and the model that produced it can produce the breakdown for free.

11) Four Checks

Four things, in this order, before a model-backed pivot goes anywhere:

1. Diagram View, tables and lines. Every table that contributes a value must be reachable from every table that contributes a filter. Islands are visible in two seconds and invisible in the numbers.
2. The repeat test. Every measure gets looked at once beside a varying row field. A column identical down its whole length is an island until proven otherwise.
3. The additivity test. For an additive measure, =SUM() over the pivot's lines equals its total row. When it does not, divide the difference by (lines − 1); if you get a whole table, you have found the island.
4. The (blank) row. Every relationship gets checked once with the many-side key on rows and COUNTROWS beside it. A (blank) row is unmatched data, and its size is how wrong every line of the report is.

🎯 Scenario: Write those four onto the sheet next to the pivot, as text, with the date they were last run. A model's joins are invisible by design, so the only durable record that somebody checked them is one you type.

12) Twelve Traps

1. A table with no relationship. Returns its grand total in every cell. The one in this article; the most common by a distance.
2. A relationship that exists but points at the wrong column. Also silent. A model with dates in three tables will accept almost any join you draw between them.
3. An inactive relationship. The dotted line in Diagram View does nothing at all until a measure wraps a calculation in CALCULATE(..., USERELATIONSHIP(...)). It looks like a relationship in the diagram, which is the problem.
4. Text keys against numeric keys. The relationship is refused, which is the good outcome; the bad outcome is somebody "fixing" it by formatting one column and not converting it, since format is not type.
5. Trailing spaces in a key. Legal relationship, no matches, everything in (blank). TRIM both sides in Power Query, not on the sheet.
6. Duplicates on the one side. Excel refuses. The fix is a dimension table of distinct values, not deleting rows you need.
7. Filtering the many side and expecting the one side to follow. Filters go one → many only. Slicing by Orders[Customer ID] will not reduce a count over Customers.
8. Implicit measures in a report with more than one column. They cannot be nested, so the second column gets built by hand and the two drift.
9. / instead of DIVIDE. #DIV/0! scattered through a pivot the first time a dimension member has no target.
10. COUNT where DISTINCTCOUNT was meant. Fourteen orders is not six customers, and both are legitimate answers to a question phrased as "how many".
11. No date table. Grouping model-backed dates by month or quarter, or writing any time-intelligence measure, wants a proper contiguous date table joined to the fact. Without it, quarters come from whatever text somebody typed.
12. Forgetting the model holds a copy. Edited sheet, unrefreshed model, and a pivot confidently reporting last week. Refresh All refreshes queries and the model in an order that usually works and occasionally needs doing twice.

What to Take Away

A relationship is a claim, and an unstated claim in the Data Model does not fail — it defaults. A measure over a table that no filter can reach returns the whole table, in every cell, forever, formatted exactly like a number that means something. That is why the region lines could each be wrong by more than £28,000 while the total row was right to the penny: the grand total is the one cell in the whole pivot where "no filter reached me" happens to be true.

The three defences are small. Open Diagram View before reading numbers. Distrust a column of identical values. Check that the lines add to the total, and when they do not, divide the difference by the number of lines minus one — £120,000.00 over two is £60,000.00, and £60,000.00 was the name of the table nobody joined.

And the star schema in section 5 is the whole cure in three rows: put the thing you slice by in its own table, join both facts to it, and slice by that. The model stops needing anyone to remember which column is safe to put on rows, which is the kind of knowledge that leaves a company on someone's last day.