3,898 / 10,045
Approved result 38.81% · 3,898 / 10,045KPI Definitions and Data Quality
Why five reports show different on-time delivery rates
A row-level reconciliation explaining why the SQL reporting mart, Power BI model, Excel management pack, operational extract and manually maintained report show different on-time delivery percentages.
Project previewOpen full image
The workpaper recalculates five on-time delivery percentages, explains every calculation and population difference, and follows one split order to the approved 38.81% result.
Five reports disagree
Excel shows 62.86%. Power BI shows 38.38%.
A distributor's reporting analyst, Power BI developer, finance business partner and operations analyst all publish on-time delivery. Their results differ because they count different records and stop the delivery clock at different events.
The metric owner must decide which percentage can be released for the period, why the reports disagree and what must change in each calculation.
Agreed resultUse 38.81% under MET-DEL-001 version 1.1.
Count 3,898 orders received in full by the original promise date from 10,045 eligible delivered orders. Update the four non-reference implementations before the next reports are issued.
The five answers first
The reported percentages only make sense when the fraction is visible.
The reconciliation starts by printing each numerator and denominator, then shows how every reported result changes under the agreed rule: one eligible delivered order, tested against final customer receipt.
15,900 / 41,433
Approved result 38.81% · 3,898 / 10,0456,314 / 10,045
Approved result 38.81% · 3,898 / 10,0457,723 / 19,890
Approved result 38.81% · 3,898 / 10,0453,881 / 9,858
Approved result 38.81% · 3,898 / 10,045Approved definitionMET-DEL-001 version 1.1
Eligible orders received in full by the original promise date / eligible delivered orders.
- Grain
- Order
- Date
- Final customer receipt
- Approved
- Director of Operations, 2026-01-05
How the calculation is brought into line
Grain, event date and extract timing explain the disagreement.
The build reads the same order, line and delivery-event records for every report. It recalculates each published percentage before applying the approved order-level final-receipt rule.
- 01Recalculate five published results
Retain each formula, grain, date field, population, filter, refresh time, numerator and denominator.
- 02Compare one approved rule
Count one delivered order and use its final customer receipt against the original promise date.
- 03Bridge every difference
Separate calculation changes from population and cut-off changes.
- 04Correct and sign off
All five implementations finish at 3,898 of 10,045 eligible orders.
Independent recalculation
Each percentage shows the fraction and rule that produced it.
3,898 / 10,045
Counts each eligible delivered order once and waits for the final customer receipt.
- Difference
- Approved reference
- Owner
- SQL developer
- Refresh
- 2026-01-03T06:00:00Z
15,900 / 41,433
Counts order lines and tests warehouse dispatch rather than completed delivery.
- Difference
- Grain and event date
- Owner
- Power BI developer
- Refresh
- 2026-01-03T06:30:00Z
6,314 / 10,045
Counts an order as on time when its first customer receipt meets the promise date.
- Difference
- Formula and event date
- Owner
- Finance business partner
- Refresh
- 2026-01-02T17:00:00Z
7,723 / 19,890
Uses warehouse dispatch and does not require a confirmed customer receipt.
- Difference
- Population and event date
- Owner
- Operations performance manager
- Refresh
- 2026-01-03T05:45:00Z
3,881 / 9,858
Uses an early cut-off and removes cancellation-after-dispatch records.
- Difference
- Timing, filter and staleness
- Owner
- Management-reporting analyst
- Refresh
- 2025-12-24T17:00:00Z
Excel reconciliation workbook
The workbook shows the numerator, denominator and correction required for each report.
The workbook contains the comparison, percentage bridge, one order trace, the five implementation rules, material record differences, corrected results, validation checks and the input register.
SQL reporting mart38.81%
3,898 / 10,045
- Approved calculation already applied0.00 pp
3,898 / 10,045
- Align the eligible order population0.00 pp
3,898 / 10,045
Approved38.81%
3,898 / 10,045
Power BI38.38%
15,900 / 41,433
- Use final receipt at order grain-28.97 pp
3,898 / 41,433
- Align the eligible order population+29.40 pp
3,898 / 10,045
Approved38.81%
3,898 / 10,045
Excel management pack62.86%
6,314 / 10,045
- Use final rather than first receipt-24.05 pp
3,898 / 10,045
- Align the eligible order population0.00 pp
3,898 / 10,045
Approved38.81%
3,898 / 10,045
Operational extract38.83%
7,723 / 19,890
- Use confirmed final receipt-19.23 pp
3,898 / 19,890
- Align the eligible order population+19.21 pp
3,898 / 10,045
Approved38.81%
3,898 / 10,045
Manually maintained report39.37%
3,881 / 9,858
- Restore filtered and late-arriving records+0.17 pp
3,898 / 9,858
- Align the eligible order population-0.74 pp
3,898 / 10,045
Approved38.81%
3,898 / 10,045
The values shown on the page come from the generated delivery records, SQL reconciliation and workbook outputs. The website does not substitute separate illustrative figures.
See exactly how the figures change
Every route reaches 38.81% with no unexplained remainder.
Power BI changes from line-level dispatch to order-level final receipt. Excel changes from first receipt to final receipt. The operational extract adds customer receipt and narrows its population. The manual report restores filtered and late-arriving records. SQL already applies the approved definition.
The agreed change in each report
Every correction has an owner, an affected report and a recorded approval.
The tolerance is 0.0000 percentage points. A report is not aligned while any difference remains.
SQL reporting martApproved reference
Retain the approved order-level final-receipt rule.
- Owner
- SQL developer
- Affected report
- Monthly service report
- Decision
- Approved for this generated sample model
- Approved
- Director of Operations, 2026-01-05
Power BIGrain and event date
Replace the line-grain dispatch measure with the approved order-grain final-receipt measure.
- Owner
- Power BI developer
- Affected report
- Power BI delivery report
- Decision
- Approved for this generated sample model
- Approved
- Director of Operations, 2026-01-05
Excel management packFormula and event date
Replace first receipt with final receipt when testing whether the order completed on time.
- Owner
- Finance business partner
- Affected report
- Monthly management pack
- Decision
- Approved for this generated sample model
- Approved
- Director of Operations, 2026-01-05
Operational extractPopulation and event date
Add final customer receipt and apply the eligible delivered-order population before publishing the metric.
- Owner
- Operations performance manager
- Affected report
- Operational delivery extract
- Decision
- Approved for this generated sample model
- Approved
- Director of Operations, 2026-01-05
Manually maintained reportTiming, filter and staleness
Refresh after the period closes and remove the manual cancellation filter so the complete approved population is used.
- Owner
- Management-reporting analyst
- Affected report
- Manually maintained service report
- Decision
- Approved for this generated sample model
- Approved
- Director of Operations, 2026-01-05
One split order explains the rule
ORD-0000001 is on time in Excel and late under the approved definition.
The order has 3 product lines. Its promise date is 2024-01-04. Excel uses the first customer receipt on 2024-01-02, while the approved rule waits for the final receipt on 2024-10-08.
The first customer receipt records 16 units on 2024-01-02, before the 2024-01-04 promise date. Excel adds 1 to its numerator and 1 to its denominator.
The final receipt records the remaining 18 units on 2024-10-08. The signed rule counts the order once, with denominator 1 and numerator 0.
- 01Order promised
2024-01-04
- 02First receipt
EVT-0000001 · 2024-01-02 · Excel adds 1 to its numerator.
- 03Final receipt
EVT-0120001 · 2024-10-08 · the order is complete after the promise date.
- 04Agreed metric decision
Denominator 1 · numerator 0 · late · Approved for this generated sample model.
Late because the final customer receipt is after the original promise date.
Missing warehouse dispatch event
On time because the Excel management pack uses the first receipt rather than final completion.
Missing warehouse dispatch event
Late because the retained manual report uses final customer receipt for this order.
What was checked
19 of 19 checks passed for the result and the page values.
- All 120,000 orders, 250,000 order lines and 180,000 delivery events agree with the generated-data manifest.
- The SQL mart, Power BI model, Excel management pack, operational extract and manually maintained report are independently recalculated from the same source rows.
- Each route has one numerator, one denominator and three explicit bridge steps to the approved result.
- The maximum unexplained bridge residual is 0.0000 percentage points.
- All 5 corrected implementations use 3,898 on-time orders from 10,045 eligible delivered orders.
- The split-order trace reproduces both the Excel on-time decision and the approved late decision.
Repeatability check
The verification script reruns the calculations and compares 16 outputs byte for byte. It also checks all 9 required workbook sheets and confirms that the bridge has no unexplained remainder.
Data and limits
This example reconciles one delivery measure; it does not describe a real organisation's performance.
The build uses deterministic non-client order and delivery data generated with a fixed seed of 240605. It covers 120,000 orders from 2024-01-01 to 2025-12-21.
Technical detailsView the data, rebuild steps and detailed limits
The data does not describe a real distributor. The DAX definition is included and independently recalculated, but it has not been run in Power BI Desktop. This project reconciles one metric and does not confirm every measure used in the five reports.
- The deterministic generated records do not measure a real distributor or its delivery performance.
- The current and approved DAX are independently re-performed but have not been executed in Power BI Desktop.
- The operational extract and manually maintained report are saved calculation snapshots rather than live connected reports.
- The project reconciles one delivery metric and does not certify every measure in the five reports.
Wholesale distribution
Order-to-Delivery Metric Dictionary
Finance reports on-time delivery at customer receipt. Operations uses dispatch date, while one report counts delivered lines. The project agrees how partial and cancelled orders should be counted.
What it helps answer: Which on-time delivery definition belongs in the monthly service report, and how should split, partial and cancelled orders be treated?
- Platform
- SQL, Excel and Power BI measure definitions
- Data scale
- 120,000 orders, 250,000 lines and 180,000 delivery events, verified
- Project status
- The Excel catalogue and SQL calculations have been checked. Matching DAX and TMDL files are included, but have not been run in Power BI Desktop.
Next step
Reconcile a disputed metric before the next report is issued.
Start with the measures people dispute or the failed checks that need clearer ownership before reporting.