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
KPI Definitions and Data Quality
On-time Delivery Metric Reconciliation
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.
Verified reconciliation workbookOpen image
The retained workpaper recalculates five on-time delivery percentages, bridges every calculation and population difference, and traces 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.
The same orders, calculated five ways
Grain, event date and extract timing explain the disagreement.
The build reads the same retained order, line and delivery-event records for every route. It recalculates each published percentage before applying the approved order-level final-receipt rule.
- 01Recalculate five published results
Retain each formula, grain, date field, source 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 keeps the records and rule that produced it.
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
- Source 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
Native Excel workpaper
The numerator, denominator and required correction remain visible.
The workbook contains the comparison, percentage bridge, retained order trace, implementation rules, material record differences, corrected results, validation and source register.
All displayed values come from the generated delivery records, SQL reconciliation and workbook outputs. They are not typed into the page as illustrative results.
A numerical bridge, not a verbal explanation
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, affected report and retained sign-off.
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 extractSource population 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.
- 01Order promised
2024-01-04
- 02First receipt
2024-01-02 · Excel adds 1 to its numerator.
- 03Final receipt
2024-10-08 · the order is complete after the promise date.
- 04Approved treatment
Denominator 1 · numerator 0 · late.
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 shared generated-data manifest.
- The SQL mart, Power BI model, Excel management pack, operational extract and manually maintained report are independently recalculated from retained 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.
Data and limits
A reproducible sample reconciliation, not a service-performance claim.
The build uses deterministic non-client order and delivery data generated with seed 240605. It covers 120,000 orders from 2024-01-01 to 2025-12-21.
Technical documentationOpen the retained data, rebuild command and detailed limitations
The data does not describe a real distributor. The DAX definition is retained and independently re-performed, but it has not been executed in Power BI Desktop. This project reconciles one 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.
Decision: 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
- Maturity
- Native Excel catalogue and executed SQL verified. Equivalent DAX and TMDL retained; Power BI Desktop execution not claimed.
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.