Why do two reports show different numbers?
Reports usually disagree because they count different business events, use different reporting dates, join records at different levels, or refresh at different times. Start by comparing those rules for one disputed measure. Rebuilding the dashboard before tracing the difference can reproduce the same problem in a new tool.
The useful question is specific: “Why does the September invoiced-sales total differ between the finance report and the operational dashboard?” Agree the period, currency and business owner before investigating.
A worked reconciliation: the same data, different totals
Illustrative example, not a client result. Suppose three invoices have values of £100, £200 and £300. All were issued in September. The first two were paid in September and each has one product line. The third was paid in October and has two product lines.
| Report rule | Total | Explanation |
|---|---|---|
| Invoice date in September | £600 | All three invoice values are included once. |
| Cash received in September | £300 | The £300 invoice was paid in October. |
| Invoice headers joined to product lines, then summed | £900 | The £300 header appears twice because it has two lines. |
The first two totals answer different questions and can both be correct. The third is wrong for an invoice-value total because the join duplicates a header. In this example, reconcile each report at invoice level before aggregating. Do not use SUM(DISTINCT amount) as a general repair: two genuine invoices can have the same value.
Revenue recognition can introduce further rules. An invoiced-sales total is not automatically an accounting revenue measure; agree that definition with the finance owner.
Trace the difference in a repeatable order
- Freeze the comparison. Record the report version, filters, refresh time, period and currency. Save the extracts in an approved location so tomorrow’s refresh does not change the evidence.
- Write the measure definition. Specify the business event, included statuses, treatment of cancellations and adjustments, and the date used to select records.
- Check the level of detail. Is each row an invoice, invoice line, customer or payment? Establish the intended unique key before joining sources.
- Compare record sets. Separate IDs found in both reports from IDs present in only one. For shared IDs, compare values. Investigate the largest differences first.
- Trace transformations. Compare counts and control totals before and after each join, filter and calculation. Check missing identifiers and unmatched records rather than silently dropping them.
- Agree the correction. Decide whether the cause is a calculation error, a source issue or two valid definitions. Assign an owner and record the decision.
Check freshness and reporting cut-offs
A live operational dashboard and an approved month-end report may legitimately disagree. Late records, time zones, reopened periods and adjustments can change the result. Publish the refresh time and period status beside the number so users know which view they are reading.
When two systems use different customer identifiers, document the matching rules. An unmatched customer should be visible as an exception; it should not disappear just to make a report balance.
Turn the investigation into controls
Keep the checks that exposed the problem: key uniqueness, record counts, unmatched IDs, totals by period and freshness. Define what happens when a check fails: who investigates, whether publishing stops and how an approved exception is recorded. A warning without an owner rarely protects the next reporting cycle.
Retest the correction on the disputed period and another representative period. Include cancelled transactions, repeated values and late-arriving records. Preserve the original comparison so a reviewer can see why the new result is trustworthy.
When is a shared data model worth building?
If several teams repeatedly recreate the same customer or financial measure, the recurring disagreement may justify shared definitions and a governed model. If a single report has one faulty join, a focused correction may be enough. The scope should follow the cause.
Our hospital finance project describes department-level P&L and budgeting built on SAP data. For your organisation, data-quality controls and reporting design can be scoped together.
Bring one disputed number
Tell us which reports disagree, who uses them and what decision is being delayed. We can scope the investigation before proposing a wider platform change. Do not send confidential extracts through the contact form; describe the issue first.
Discuss your reporting problemRelated reading
When to automate spreadsheet reporting · Dashboards and data questions need the same foundation