One margin bridge, three tools

January and February of a small field-service book: $3,950 of completed revenue, $2,950 of direct cost, $1,000 of gross profit. This post builds that bridge once in Databricks, traces one work order through it with SQL, and recomputes the margin in Power BI. The point is that three tools land on the same number, and that every step between them can be checked by hand.

Synthetic data: an invented HVAC-style shop with six work orders. No real company, customer or invoice.

Databricks: bronze, silver, gold

The raw extract lands in bronze untouched. Every field is text, the status arrives as Completed, completed and COMPLETED, and work order WO-1001 appears twice: a first version billed $1,200, and a correction two days later billed $1,450. A one-line count turns that into a number before any fix: 6 source rows, 5 distinct work orders, 1 duplicate version.

Silver casts the types, lowercases the status, and keeps one row per work order with ROW_NUMBER: newest source timestamp wins. That rule is written down, not left to load order.

Gold answers the business question: one row per month, completed orders only, margin computed as summed profit over summed revenue, with a guard so a month with no revenue reports no margin instead of failing the job. January lands at $2,350 of revenue and $1,000 of gross profit, 42.6 percent. February's one completed order broke even.

month	revenue	gross profit	gross margin
2026-01	2,350.00	1,000.00	42.6%
2026-02	1,600.00	0.00	0.0%

The load that is safe to run twice

Rebuilding every table from scratch works at six rows and fails at a billion. An incremental MERGE applies only new and changed records, keyed on the work order id, and updates a row only when the incoming source timestamp is newer than the stored one. A test batch held a newer WO-1002 (updated), a stale WO-1003 (ignored by the guard) and a new WO-1006 (inserted).

Then the same batch runs again. Nothing updates, nothing inserts, and total rows still equal distinct keys.

That equality, six and six before and after the rerun, is the proof that a retried job or a batch delivered twice cannot double count.

SQL: trace one order to the dashboard

The correction to WO-1001 is worth tracing because it changes the answer. On the first record the order made $420 of gross profit, a 35.0 percent margin. The corrected record adds $250 of revenue and $30 of cost.

Silver holds only the corrected version: $640 of gross profit on $1,450, 44.1 percent. In gold the order stops being a row. It is $1,450 of January's $2,350 and $640 of its $1,000, so lineage means being able to point at it inside the total.

A tie-out query sums revenue and direct cost over the resolved silver rows with the same filter as gold: completed orders serviced in January 2026.

Power BI: recompute it, do not copy it

The report imports the silver work orders at order grain, not gold's stored total, so DAX recomputes the margin independently. Two base measures sum the raw columns. Everything else composes from them by name:

Measure	Definition
Revenue	Sum of the revenue column
Direct Cost	Sum of the direct cost column
Gross Profit	Revenue minus Direct Cost
Gross Margin %	Gross Profit over Revenue, blank when Revenue is 0

Each definition lives in one place, so revenue cannot mean one thing on a summary card and another on a detail page. A card filtered to January and completed orders reads 42.6 percent.

Three paths, one number. Agreement between independent computations is stronger evidence than one number copied forward.

Two traps the tie-out catches

Averaging percentages. January's two orders run at 44.1 and 40.0 percent. Their average is 42.1 percent; the real margin, summed profit over summed revenue, is 42.6. The gap is small here because the orders are similar in size. With $50 at 80 percent next to $5,000 at 20 percent, the average says 50 percent and the business runs at 20.6.

Rounding. Gold stores 42.6. DAX holds 0.42553 and formats it as 42.6 percent. They agree on screen and differ underneath, so comparing two rounded percentages can flag a false break or hide a real one. Reconcile revenue and gross profit, the two dollar components, and the ratio follows.

The checklist

• Count the duplicates before writing the fix.
• Resolve them with one written rule.
• Normalize categories before any filter reads them.
• Guard every division that can hit zero.
• Key incremental loads on a business key plus a newer-than guard, and prove it by running the load twice.
• Recompute the headline number a second, independent way.
• Reconcile dollars, not rounded percentages.

The same 42.6 percent is the value a CI test asserts in a Power BI report under change control.