In Part 5 we taught the agent the shape of gold_dw: the joins, the grain, the exact values to filter on. By the end it got all five of my trap questions right. So I gave it a harder kind of question, the kind a finance person actually asks: net revenue, units sold, return rate, gross margin, net profit.

It answered every one in seconds, with no errors. Then I asked the same five questions again, and only gross margin came back the same. Net revenue was wrong both times, with a different wrong number each time. Units sold was right once and wrong once. And net profit was a number the data can't produce at all, also twice, also different.

None of this is a SQL problem. The agent knows the tables now. What it doesn't know is what your business terms mean, so it fills the gap with the textbook definition, and the textbook doesn't know how your warehouse stores returns.

The test setup: the same Contoso Sales Agent from Part 5, on gold_dw, with Part 5's final data source instructions and nothing about business terms. I asked the five questions twice: once through the published agent's MCP endpoint, with a fresh session per question, and once as a single conversation in the agent's chat in Fabric. I checked every answer against the queries below.

First, the thing nobody tells you about returns

Before writing a single definition, I looked at how a return actually sits in dbo.Sales. This query groups every order line by its two quantity columns:

SELECT [Sales Quantity], [Return Quantity],
       COUNT_BIG(*)         AS order_lines,
       SUM([Sales Amount])  AS sales_amount,
       SUM([Return Amount]) AS return_amount,
       SUM([Total Cost])    AS total_cost
FROM dbo.Sales
GROUP BY [Sales Quantity], [Return Quantity];

Two rows. That's the whole table. Every order line is exactly one unit, and a line is either kept or returned. When it's returned, [Sales Quantity] and [Sales Amount] drop to zero and the money moves to [Return Amount].

So [Sales Amount] is already net of returns. Hold on to that, because the textbook formula for net revenue is "sales minus returns".

The other thing to notice is the last column. Returned lines still carry 13.7 million of [Total Cost]. That matters for margin, and we'll get there.

Question 1: "What was our net revenue in 2025?"

What it said: two different things, and both were wrong.

Through the published endpoint it said 837,760,143.81. When I asked the exact same question in the agent's chat, it built a little income statement instead:

What's true:

SELECT SUM(s.[Sales Amount])                          AS sales_amount,
       SUM(s.[Return Amount])                         AS return_amount,
       SUM(s.[Sales Amount]) - SUM(s.[Return Amount]) AS sales_minus_returns
FROM dbo.Sales AS s
JOIN dbo.[Date] AS d ON d.Date_Key = s.Date_Key
WHERE d.[Year] = 2025;

The first answer did exactly what a sensible analyst would do with no context: sales minus returns. In this warehouse, that subtracts every return twice. It's 9.8 million short, about 1.2%, which is the dangerous size. A 74x fan-out like Part 5's gets noticed in the first meeting. A number that's 1% low gets put in a board deck.

The second answer labeled [Sales Amount] as GrossSales and took discounts off as well. But [Sales Amount] is already after discounts too. On the kept lines, adding the discount back lands exactly on units times list price:

SELECT SUM(s.[Sales Amount])                      AS sales_amount,
       SUM(s.[Discount Amount])                   AS discount_amount,
       SUM(s.[Sales Amount] + s.[Discount Amount]) AS sales_plus_discount,
       SUM(s.[Sales Quantity] * p.[Unit Price])    AS units_x_list_price
FROM dbo.Sales AS s
JOIN dbo.Product AS p ON p.Product_Key = s.Product_Key
WHERE s.Date_Key BETWEEN 20250101 AND 20251231 AND s.[Return Quantity] = 0;

So 752,294,670.81 is 95.2 million (11%) short. The real net revenue is the plain sum, 847,541,267.31. And with no definition to anchor it, the agent rebuilt "net revenue" from scratch each time it was asked, which is how one question got two answers.

The lines I'd add:

- Returns are already removed from the sales columns: a returned order line has [Sales Quantity] = 0
  and [Sales Amount] = 0, and the refund is in [Return Amount]. Never subtract [Return Amount] or
  [Return Quantity] from [Sales Amount] or [Sales Quantity].
- Revenue, sales and net revenue all mean SUM([Sales Amount]): after discounts and after returns.
  Never subtract [Discount Amount] from it. Gross sales (at list price, before discounts):
  SUM([Sales Amount] + [Discount Amount]) on lines where [Return Quantity] = 0.

Question 2: "How many units did we sell in 2025?"

What it said: 3,584,098 through the endpoint. Correct. In the chat it said 3,550,970, and told me exactly why:

Same query shape as Question 1, three candidate answers:

"Net units = Sales Quantity minus Return Quantity" is Question 1's double count again: [Sales Quantity] is already 0 on the 33,128 returned lines.

I do like that it stated its assumption up front. But a clearly stated wrong assumption is still a wrong answer, and most people who read "3,550,970" won't read the line above it. It also means the endpoint's correct answer was a guess that landed, not a rule.

Question 3: "Which product category has the highest return rate?"

What it said: through the endpoint, Music, Movies and Audio Books, at 1.22%. In the chat, TV and Video, at 1.21%, after this:

The question never mentioned a year. The chat carried "2025" over from earlier in the conversation, and said so. Fair enough. The endpoint started fresh, so it used all the years.

This is the one I find most interesting, because there's no single right answer. Here's every category across all years, measured the three ways I've seen people measure it:

SELECT RTRIM(p.[Product Category]) AS category,
       SUM(s.[Return Quantity])   AS returned_units,
       CAST(100.0 * SUM(s.[Return Quantity]) / COUNT_BIG(*) AS decimal(6,3))                      AS by_units_pct,
       CAST(100.0 * SUM(s.[Return Quantity]) / NULLIF(SUM(s.[Sales Quantity]), 0) AS decimal(6,3)) AS kept_base_pct,
       CAST(100.0 * SUM(s.[Return Amount])
            / (SUM(s.[Sales Amount]) + SUM(s.[Return Amount])) AS decimal(6,3))                   AS by_value_pct
FROM dbo.Sales AS s
JOIN dbo.Product AS p ON p.Product_Key = s.Product_Key
GROUP BY RTRIM(p.[Product Category])
ORDER BY by_units_pct DESC;

Both runs used the middle column: returned units divided by kept units. That's a small denominator mistake caused by the same returns quirk. You have to add the returns back to get "units that went out the door".

The bigger issue is the last column. Measure by value and the winner flips to Home Appliances, and Cell phones jumps from last to fourth, because the phones that come back are the expensive ones. Narrow it to 2025, as the chat did, and the leaders change again:

Four reasonable readings of one question, four different winners: Music, Home Appliances, TV and Video, Cameras. In 2025 the top five are within 0.03 points of each other, so the honest answer is "it's a near tie". Neither run said that.

The line I'd add, and this one is a decision, not a fact:

- Return rate: by units by default, SUM([Return Quantity]) / (SUM([Sales Quantity]) + SUM([Return Quantity])).
  By value when the user says value, amount or revenue:
  SUM([Return Amount]) / (SUM([Sales Amount]) + SUM([Return Amount])). Always say which one you used.

Question 4: "What was our gross margin in 2025?"

What it said: 52.03%, both times. Correct, given a choice it made on its own. The chat also volunteered a "net margin" of 45.96%, built on Question 1's 752 million:

Three defensible-looking versions:

C is Question 1's double count again. A versus B is a real business question: when a product comes back, is its cost still a cost? If it goes back on the shelf, arguably not. If it's written off, definitely yes. The data can't tell you, so someone has to decide.

I went with A. We don't know whether returned stock gets resold, and I'd rather report a margin that's half a point conservative than one that's flattering. The agent happened to pick A too. Now it'll pick A on purpose.

- Gross margin = SUM([Sales Amount]) - SUM([Total Cost]). Gross margin % = that / SUM([Sales Amount]).
  [Total Cost] includes the cost of returned lines; keep it in.

Question 5: "What was our net profit in 2025?"

What it said: through the endpoint, "Your total net profit in 2025 was 440,973,689.11." In the chat, 345,727,092.61:

The first is gross margin, the A number from Question 4 to the cent, relabeled as net profit. The second is gross margin with discounts and returns subtracted again. Neither is net profit. Net profit needs operating expenses, marketing, shipping and tax, and none of those exist in gold_dw.

The chat even named operating expenses and taxes in its assumption, then printed a number labeled NetProfit anyway. The honest answer is "I can't calculate that from this data". This is the "state what isn't modeled" part of the job, and it's the one I'd never skip. We'll see below that the wording of this line matters more than I expected.

Getting the fixes to stick

I collected those lines into a "Business terms and measures" section, added it to the end of Part 5's instructions, republished, and re-asked everything through the endpoint. One run, five right answers. Done, I thought.

It wasn't. Two things went wrong on the way:

• The chat didn't change. Asked in the Fabric chat right after publishing, it repeated its old net revenue (752,294,670.81) and net profit (345,727,092.61), "Assumption:" line and all.
• My "offer gross margin instead" backfired. I moved the definitions above "When asked about" and added question-shaped lines there. Then I asked every question three more times. Net revenue, units and gross margin were right every time. But in 3 of 4 runs, net profit came back as "net profit (gross margin): 440,973,689.11". I'd told it to offer gross margin, and it obliged by putting gross margin under the net profit label.

So the net profit line now forbids the number instead of suggesting a substitute. Here's where the instructions ended up, with Part 5's lines shortened to ...:

## General knowledge
...
- Business terms (revenue, units, return rate, margin, profit) are defined under "Business terms and measures".
  Use those definitions exactly. Never replace them with a textbook formula or an assumption of your own.

## Table descriptions
...

## Business terms and measures
- Returns are already removed from the sales columns: a returned order line has [Sales Quantity] = 0
  and [Sales Amount] = 0, and the refund is in [Return Amount]. Never subtract [Return Amount] or
  [Return Quantity] from [Sales Amount] or [Sales Quantity].
- Revenue, sales and net revenue all mean SUM([Sales Amount]): after discounts and after returns.
  Never subtract [Discount Amount] from it. Gross sales (at list price, before discounts):
  SUM([Sales Amount] + [Discount Amount]) on lines where [Return Quantity] = 0.
- Units sold: SUM([Sales Quantity]). Units returned: SUM([Return Quantity]).
- Return rate: by units by default, SUM([Return Quantity]) / (SUM([Sales Quantity]) + SUM([Return Quantity])).
  By value when the user says value, amount or revenue:
  SUM([Return Amount]) / (SUM([Sales Amount]) + SUM([Return Amount])). Always say which one you used.
- Gross margin = SUM([Sales Amount]) - SUM([Total Cost]). Gross margin % = that / SUM([Sales Amount]).
  [Total Cost] includes the cost of returned lines; keep it in.
- Not in this data: operating expenses, marketing spend, shipping, tax, inventory, targets.
  Net profit, operating profit and EBITDA can't be calculated. Say so. Gross margin is not net profit:
  never present it, or any other number, as net profit.

## When asked about
- Net revenue, revenue or sales: SUM([Sales Amount]) alone. It is already after discounts and returns, so never
  subtract [Discount Amount] or [Return Amount], not even as a stated assumption.
- Units sold: SUM([Sales Quantity]) alone. Never subtract [Return Quantity].
- Net profit, operating profit or EBITDA: answer that it can't be calculated because operating expenses,
  marketing, shipping and tax aren't in this data. Return no number for it, not even with a stated assumption.
...

Three runs of every question against that version, through the endpoint:

The net profit row deserves an asterisk. The agent did refuse every time, but the reasons it gave were wrong: twice it claimed the warehouse only exposes customers, products, dates and currency, and once it returned a generic "There's content here I can't work with". As far as I can tell, data source instructions shape the query the agent writes, not the sentence it says afterwards. The query step declined as told, and the answer step had to invent an explanation. Getting the reply right is a job for agent instructions, which is Part 9 (and Part 10 for saying no well).

The return rate only named its definition once ("1.20% of units sold"). Same pattern as Part 5's "always say": calculation rules stick, wording rules wobble.

Trade-offs: definitions are decisions, so don't let the model make them

The most useful lines in that block aren't the formulas. They're the decisions: units before value, returned cost stays in, net profit is out of scope. The agent will always fill a gap with something plausible, and Question 5 shows how far "plausible" can drift from "true". Get these definitions from whoever owns the numbers, usually finance. If your company already has a metrics glossary, copy its wording.

Watch the phrasing of every "instead". "Offer gross margin instead" read to me like a helpful fallback. To the agent it read as permission to answer with gross margin.

Keep the list short. If you find yourself defining forty measures in prose, they belong in a semantic model where they're defined once in DAX. That's the comparison in Part 15.

And test like you don't trust it. Re-ask all the questions after every change, and ask each one more than once. My two "before" runs disagreed on four of five questions, and one clean "after" run hid a net profit problem that only showed up on the second, third and fourth tries.

What's next

Next up: Time Intelligence for Data Agents: YTD, YoY, Rolling Totals. Now that "revenue" means one thing, we'll see what happens when the agent has to compare it across time, where "this year" quietly depends on which day you ask. See you there.

Sources

• Data agent configurations (data source instructions and the suggested template): https://learn.microsoft.com/fabric/data-science/data-agent-configurations
• Best practices for improving data agent query generation: https://learn.microsoft.com/fabric/data-science/data-agent-configuration-best-practices