Key takeaways
- Name the source of truth for each metric, including the report and extract time.
- Reconcile at grand total, segment, and row level; matching totals can hide offsetting errors.
- Record expected values and tolerances so a pass is evidence, not an opinion.
- Re-run the checks after the first production refresh and after any model change.
On this page
Define the source of truth for each metric
Reconciliation needs a reference. For revenue that is usually the ledger; for pipeline it may be the CRM; for traffic it is the analytics platform. One dashboard can have several references, and that is acceptable as long as each metric names its own.
Record the exact report, filters, period, and extract time used as the reference. A total pulled at nine in the morning and a dashboard refreshed at noon will differ if transactions arrived in between. Most reconciliation disputes are timing disputes wearing a data costume.
Test at three levels
A grand total can match while two errors cancel: a duplicated segment offset by a dropped region. Test the total, then every segment that users will filter by, then a sample of rows at the lowest grain.
For each level, compare the dashboard value with the reference value, the difference, and the percentage difference. Investigate any segment that differs even when the total matches. The segment test is where join and filter problems usually surface.
Know the usual causes of mismatch
Most reconciliation failures come from a short list of data problems. Learn to recognise the shape of each one, then design a test that isolates it rather than adjusting the dashboard until the number looks right.
The table pairs each cause with the symptom it produces and the check that confirms it. A single symptom can have more than one cause, so rule them out in order.
| Cause | Symptom | Test |
|---|---|---|
| Duplicate source rows | Total and row count too high | Count distinct keys |
| Fan-out from a join | Inflated amounts, repeated rows | Compare pre-join and post-join totals |
| Inner join drops | Total too low, members missing | Left join and count unmatched keys |
| Date boundary | One day or month shifts | Compare first and last period days |
| Time zone | Evening transactions move days | Test a known late transaction |
| Currency | Total differs by rate | Reconcile in source currency first |
| Refunds and cancellations | Gross matches, net does not | Test gross and net separately |
| Late-arriving data | Small drift after refresh | Compare data-through timestamps |
Set expected values and tolerances
Write the expected value into the test sheet before looking at the dashboard. This prevents the common failure where the tester adjusts the expectation to match the output.
Use exact matches for counts and currency totals at the cent, unless the source itself rounds. For rates and averages, define a tolerance and explain it. Ratios of large numbers can differ in the last decimal through rounding alone; a two per cent gap in a conversion rate usually cannot.
Test the edges and the access paths
Check empty periods, a single day, the first and last day of the history window, nulls, negative values, and the largest customer or transaction. These cases expose date filters, division by zero, and formatting that hides a problem.
Test access roles as well. A regional manager should see their region and not the national total if that is the rule. Test exports, scheduled subscriptions, and mobile views; each can use a different query path from the page the builder tested.
Sign off with evidence
A launch is not the end of reconciliation. The first real refresh, a source schema change, or a new filter can break a total that was correct at sign-off. Keep the test sheet with the dashboard and re-run the critical rows when the model changes.
The checks below are the minimum record for a launch. They also give the next analyst a starting point when a user reports a number that looks wrong.
- Source, report, period, and extract time for every reference value.
- Grand total, segment totals, and row sample results in one sheet.
- Differences explained, accepted, or fixed; no unexplained variance.
- Named sign-off from the metric owner, not only the builder.
- Reconciliation repeated after the first production refresh.
- Checks added to a regression list for future model changes.
Frequently asked questions
What variance is acceptable in a dashboard reconciliation?+
Zero for counts and currency totals, unless the source rounds. For rates and averages, agree a tolerance before testing and write down why it is appropriate. An unexplained difference is never acceptable, even if it is small.
What if the source system is wrong?+
Agree the correction path with the system owner and do not encode a guess in the dashboard. If the dashboard must show a corrected figure, label the adjustment and keep the reconciliation note visible.
How many rows should be sampled?+
Enough to cover every segment and edge case, not a fixed number. A sample that includes the largest customer, a refund, a null, and a first-day transaction is more useful than a random extract of a thousand rows.
Who should sign off before launch?+
The metric owner and the builder. Financial metrics should also be signed off by finance. The sign-off should reference the test sheet, not just the dashboard link.
Need help applying this?
Get help with Dashboards & BI.
Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.
Free KPI definition templateRelated reading
KPI dashboard design: how to choose metrics that matter
A good KPI dashboard starts with decisions, not charts. Learn how to choose, define, and present metrics people will actually use.
Accessible dashboards: colour, labels, and focus
Design dashboards that stay readable without colour: contrast, redundant encoding, direct labels, keyboard focus, and screen reader structure.