The gross-margin reconciliation report: from raw record to dashboard

This is the gross-margin reconciliation report I run to prove a dashboard number is right. A card can say January's gross margin was 42.6 percent, but that number is only trustworthy if I can say exactly where it came from: which source records fed it, which business rules shaped it, which duplicate got resolved, and where a rounding step could make it look wrong.

Here I trace one work order, WO-1001, and one metric, gross margin percent, through a raw record, a resolved version, a monthly aggregate, and a Power BI card, and confirm all four stops agree on 42.6 percent.

It runs on the same synthetic medallion dataset I built in the monthly gross-margin bridge report: a fictional field-service company I invented for this exercise, an HVAC-style shop with work orders, revenue, and cost. None of it is drawn from a real company. The rows are the small inline ones from that post, so every total here is a checkable property you can reproduce.

The reconciliation, at a glance

Same filter at every layer: completed work orders serviced in January 2026.

Metric	Silver (SQL sum)	Gold (stored row)	Power BI card (DAX)
Revenue	2350.00	2350.00	2,350
Direct cost	1350.00	1350.00	1,350
Gross profit	1000.00	1000.00	1,000
Gross margin %	42.6	42.6	42.6

Every cell ties. The gold column is a special case worth naming: gold stores revenue and gross profit directly, so its direct cost is the difference, 2350 minus 1000, which is 1350. WO-1001's 1450 in revenue and 640 in gross profit are inside all three revenue and profit totals. That is what lineage lets me do: point at the order inside the aggregate.

What the numbers say

• January's blended gross margin is 42.6%, on $2,350 revenue and $1,000 gross profit ($1,350 direct cost).
• WO-1001 alone is 62% of January's revenue ($1,450 of $2,350) and 64% of its gross profit ($640 of $1,000).

WO-1001WO-1002

• Resolving WO-1001's duplicate swung its own margin 9.1 points: from 35.0% on the stale first record to 44.1% on the corrected one.

• Averaging the two orders' percentages instead of blending by dollars misses by 0.5 points on this book (42.1% versus 42.6%), and by 29.4 points on the extreme example later in this post (50.0% versus 20.6%).
• Three independent computations, silver's SQL sum, gold's stored row, and the Power BI DAX card, all land on 42.6%.

Why this report matters

• A challenged number gets an answer, not a re-assertion. When someone asks why the dashboard says 42.6%, I can point to the resolved rows and the aggregation rule instead of repeating the total.
• It protects against a silent duplicate. WO-1001 arrived twice; a report without a stated resolution rule risks summing both versions or silently keeping whichever loaded last.
• It fits month-end close as a standing check. The same reconciliation runs on every close, not only when a number looks wrong.
• It leaves an audit trail. The correction that moved WO-1001 from a 35.0% to a 44.1% margin is a named, dated event in the source data, not an unexplained jump in a monthly total.

The one order and the one number

WO-1001 is the interesting order to trace because it exercises the whole governance story: it arrives in the raw feed twice, gets resolved to one version in silver, and then folds into January's monthly aggregate that the dashboard reads.

Here is the raw record as the source system handed it over, both versions of WO-1001. Every field arrives as text on landing:

work_order_id	service_date	customer_id	technician_id	status	revenue	direct_cost	source_updated_at
WO-1001	2026-01-05	C-01	T-3	Completed	1200.00	780.00	2026-01-06 09:00:00
WO-1001	2026-01-05	C-01	T-3	COMPLETED	1450.00	810.00	2026-01-08 11:15:00

The fields, defined once:

• work_order_id: the business key, WO-1001. Identity is tracked on this, not on a load id.
• service_date: when the work happened, 2026-01-05.
• customer_id and technician_id: who it was for and who did it.
• status: the lifecycle state, arriving in inconsistent casing.
• revenue: billed amount, still a string on arrival.
• direct_cost: cost directly attributable to the job, still a string on arrival.
• source_updated_at: when the source last touched this version. This is the tiebreaker that decides which version wins.

Notice WO-1001 appears twice. A first version billed 1200 on 2026-01-06, then a correction on 2026-01-08 bumped it to 1450. Both are real facts about the source, and lineage starts by refusing to hide either.

Following the record through four stops

Bronze: preserve, correction and all

Bronze's only job is fidelity. Both versions of WO-1001 sit here untouched, revenue still a string, casing still mixed, nothing resolved. A one-line count makes the duplicate a number rather than a surprise: for WO-1001, source rows are 2 and distinct keys are 1, so exactly one version needs resolving. That is deliberate. If a downstream number ever looks wrong, bronze is the layer that still holds what actually landed.

Silver: resolve to the newest version

Silver types every field and keeps one row per business key, newest source timestamp wins. The medallion post shows the ROW_NUMBER that does this. For WO-1001, the 2026-01-08 correction outranks the 2026-01-06 original, so silver holds exactly one WO-1001:

work_order_id	service_date	status	revenue	direct_cost	source_updated_at
WO-1001	2026-01-05	completed	1450.00	810.00	2026-01-08 11:15:00

Now the order has a settled shape. Its own gross profit is 1450 minus 810, which is 640.00, and its own gross margin is 640 over 1450, which is 44.1 percent. Hold that number. It is the last time WO-1001 has a margin of its own, because gold is about to blend it with its neighbors.

Gold: fold into the monthly aggregate

Gold answers the business question: one row per month, completed orders only, with revenue, gross profit, and gross margin percent. WO-1001 stops being a row and becomes part of a sum. In January it joins WO-1002 (revenue 900, cost 540); the scheduled order WO-1003 is filtered out by the completed-only rule (the cancelled order WO-1005 falls in February).

month	revenue	gross_profit	gross_margin_pct
2026-01	2350.00	1000.00	42.6

WO-1001 contributed 1450 of the 2350 revenue and 640 of the 1000 gross profit. Its identity is gone, but its dollars are fully accounted for, and the monthly margin is 1000 over 2350, which is 42.6 percent.

Power BI: recompute the same number in DAX

The dashboard does not read the gold aggregate directly; I import the fact table at work-order grain instead. Revenue and direct cost are base measures, gross profit is their difference, and gross margin percent is gross profit divided by revenue, each one composed from the measure before it rather than repeating the underlying sum.

Tools I'd use: Power BI, composed DAX measures.

I load the silver work orders as that fact table, so Power BI recomputes margin from the same resolved rows rather than trusting gold's stored number. A card filtered to January 2026 and Status equal to Completed reports 42.6 percent. Gold computed the number in SQL, Power BI recomputed it in DAX from the fact grain, and they land on the same value: two independent paths agreeing is much stronger evidence than one number copied forward.

The reconciliation tie-out

The silver figures in the table above come from a query that sums revenue and direct cost across the resolved rows for the same filter, completed work orders serviced in January 2026. From those two sums it derives gross profit as the difference and gross margin percent as gross profit divided by revenue, rounded for display.

Tools I'd use: SQL against the warehouse.

Why margin is sum of profit over sum of revenue

The gold query computes margin as the sum of gross profit over the sum of revenue, not as the average of each order's margin. That choice is not a style preference. The average is a different, wrong number.

The two completed January orders have margins of their own: WO-1001 at 44.1 percent (640 over 1450) and WO-1002 at 40.0 percent (360 over 900). Average those two percentages and you get 42.1 percent. Divide summed profit by summed revenue and you get 42.6 percent. They disagree because averaging weights a small order the same as a big one, while the correct margin lets the dollars vote.

The gap is small here because the two orders are close in size. It is not always small. Imagine one order of 50 dollars at 80 percent margin (40 dollars of profit) and one order of 5,000 dollars at 20 percent margin (1,000 dollars of profit). The average of the two percentages is 50 percent. The true blended margin is 1,040 over 5,050, which is 20.6 percent. The big low-margin order dominates the dollars, and the simple average hides that completely. Margin is a ratio of totals, so it has to be built from totals.

Rounding lives at each layer

Each layer rounds in its own place. Gold stores gross_margin_pct rounded to one decimal place, which is 42.6. Power BI's margin measure computes the full-precision 0.42553 and the card formats it to 42.6 percent for display. The two agree at display precision, but the raw values are 42.6 and 42.55, which are not equal.

That is not an error, but it is a trap. If a reconciliation compares two independently rounded percentages, it can flag a mismatch that is only rounding, or worse, hide a real one under a rounding tolerance.

• Remember: reconcile on the raw dollar components, revenue and gross profit, and treat the percentage as a display of them. Tie out the numerator and the denominator, and the ratio takes care of itself.

My process on the job

• Pin down the business key and the one metric before touching any layer boundary.
• Preserve the raw feed untouched at the first landing zone, corrections included.
• Resolve duplicates with one explicit, logged rule (newest source timestamp wins here), never a silent pick.
• Aggregate for the business question with the filter stated next to the number.
• Recompute the number a second independent way in the reporting layer, rather than trusting a copied-forward value.
• Reconcile on the raw dollar components, revenue and cost, not the rounded percentage.
• Package the trace as a repeatable report tied to month-end close, not a one-off investigation.
• State the report's limits out loud.
• This traces a small, synthetic, invented field-service company, not a production warehouse or a large-team governance program. The skill on display is narrow and real: given a number, I can find its source and name its limits.

Key takeaways

• Lineage is pointing, not guessing. I can take one number off a card and name every step behind it: which raw rows fed it, which duplicate version won, how it aggregated, and where rounding could mislead.
• Each layer has one job. Bronze preserves what landed (both versions of WO-1001), silver resolves to the newest version by source timestamp, and gold folds the order into the monthly aggregate.
• Blend margin from totals, not percentages. Summed profit over summed revenue is the real margin. Averaging per-order percentages lets a small order count as much as a big one, which can be badly wrong.
• Reconcile on the dollar components, not the rounded percentage. Tie out revenue and gross profit and the ratio takes care of itself. Two independently rounded percentages can disagree by rounding alone.
• Two paths agreeing beats one number copied forward. Gold computed the margin in SQL and Power BI recomputed it in DAX from the fact grain, and they land on the same value.

Related posts

Other walkthroughs built on the same invented field-service company:

• The monthly gross-margin bridge report, where the bronze, silver, and gold tables traced here get built.
• Machine learning on that lakehouse: a legible completion model and a customer segmentation.
• Six enterprise SQL patterns for dedup, idempotent loads, and a Type 2 dimension.
• The Power BI star schema and DAX measure dictionary behind the card at the end of this trace.
• Putting the Power BI project under version control, so a measure edit gets a diff, a review, and an audit trail.