To explain a difference between a dashboard and a business total, compare the same records, under the same definition and at the same point in time. Then reconcile the rows before changing the calculation. The difference might come from a filter, an order repeated by a join or an update that has not arrived. A mismatch alone does not establish a system failure.
This method suits a small service business tracking orders in both an operations system and a report. It produces an explainable list of differences, with the affected identifiers, amounts and next actions. Exports and a spreadsheet may be enough for the first investigation.
Define what the number is meant to measure
“This month’s total” is not a complete definition. One team may mean confirmed orders, another issued invoices and another payments received. Start with a short metric definition approved by the person responsible for it:
- Object and level of detail: one row per order, invoice or product line, with a stable identifier that is unique at that level.
- Population: location, team, record type and included or excluded statuses.
- Amount: before or after tax, treatment of discounts, currency and rounding rule.
- Date: creation, confirmation, invoicing or payment; inclusive start, exclusive end and a time zone when the field includes a time.
- Data state: reference time, last known complete refresh and treatment of late changes.
- Calculation and owner: sum, distinct record count, average or ratio, with a designated source and someone who can approve exceptions.
The example below measures the before-tax value of orders dated in October 2026 with confirmed status at extraction, in Canadian dollars, with one row per order. It does not measure cash received or accounting revenue. If the reports describe different metrics, resolve their definitions or labels before trying to make their numbers equal.
Collect evidence before changing the report
Keep the displayed total, active filters, observation time and an authorized detailed export from each system. Add the refresh history and, when available, the import log, processed row count and rejected records. A screenshot of a chart does not show which records contributed to the calculation.
Request read access and working identifiers instead of customer contact details. Check that each export includes all authorized rows: pagination, export limits, aggregation and user permissions may change the observed scope. Compare under the same business permissions; do not remove access restrictions to make totals agree.
An existing spreadsheet and two exports may avoid a purchase. Access to detailed data, a connector, a reporting licence or technical assistance may still be needed. Check export permissions and costs in your tools; there is no universal price for this investigation.
Keep dated working copies of both extracts. If the source continues changing, record the timing difference instead of treating the exports as simultaneous. A successful last run does not, by itself, prove that every expected record is present.
Check scope before the formula
First reproduce the filters in the metric definition. Check visual, page and report filters, interactive selections and exclusions in the query. Power BI supports filters at different levels, so inspecting only the visible slicer may miss part of the context. Source: Microsoft report filters, checked November 4, 2026.
Next compare the date field actually used and the data state loaded into the report. One report may classify an order by creation date while another uses confirmation date. A timestamp can also move to a different calendar day when displayed in another time zone. Our guide to imported dates and time zones explains that distinction without arbitrarily adding a day.
Depending on the connection mode, the model may retain an imported copy or query its source differently. Check the actual mode and refresh history before forcing a refresh. An incomplete import, an out-of-scope record and a newer record are different explanations. Source: Microsoft data refresh, checked November 4, 2026.
Reconcile identifiers and amounts
- Count rows and distinct identifiers. An order appearing twice needs an explanation. If the intended level is order lines, several rows per order may be correct; use a key that identifies each line.
- Compare identifier sets. Isolate records found only in the business source and records found only in the report. Keep their cause open until verified; do not invent records to fill a gap.
- Compare values for shared identifiers. Check amount, currency, status, selected date and occurrence count. A missing value is not automatically zero.
- Group differences by demonstrated cause. Separate filters, period, repetition, missing data and changed amounts. Keep supporting evidence and an owner for each proposed correction.
- Recalculate from the reconciled detail. If a record has several problems, state the correction order: their effects may not be independent.
A join connects rows from two tables. In a flattened export or query, when an order has several service visits, joining those visits can repeat the order amount in the result. The correct calculation depends on the intended level of detail. Microsoft’s star-schema guidance distinguishes tables used to filter and group data from tables used to summarize facts. Source: Microsoft star-schema guidance, checked November 4, 2026.
Do not fix this with a sum of distinct amounts: two different orders may legitimately have the same value. Deduplicate by the business key and an approved rule, or calculate at the right level before joining detail. A ratio or distinct-count total must also follow its own definition; adding the numbers displayed by category is not always valid. Microsoft’s DISTINCTCOUNT documentation explains why an order present in several categories is counted only once in the overall total.
An illustrative discrepancy you can calculate
In a frozen fictional dataset, four orders dated in October and marked confirmed in the extract qualify: S100 is $120.00, S101 $240.00, S102 $360.00 and S103 $280.00, all before tax in CAD. The expected result is $1,000.00 across 4 orders. The period includes October 1 and excludes November 1, using order date.
The register also contains an October draft order worth $150.00, a $200.00 order dated November 1 with confirmed status, a $175.00 order from September 30, a cancelled October order worth $90.00 and an October order in USD. All are excluded from this metric. No exchange rate is applied.
| Isolated variation from the correct dataset | Observed total | What the detail reveals |
|---|---|---|
| S100’s amount repeated across 2 detail rows and S101’s across 3 | $1,600.00 | 7 rows but only 4 distinct orders; an extra $600.00 is counted. |
| The $150.00 draft is included | $1,150.00 | An out-of-scope status. |
| The November 1 order is included | $1,200.00 | An incorrect period boundary. |
| S102 is missing | $640.00 | An expected order is absent; $360.00 remains unexplained. |
| S100 increased by $50.00 and S101 reduced by $50.00 | $1,000.00 | The total looks correct, but two amounts are wrong. |
Each row starts again from the correct dataset; these are not consecutive steps in a cumulative correction. In the join example, the incorrect calculation is 120 × 2 + 240 × 3 + 360 + 280. Reconciliation must restore one contribution from each qualifying order. This is an illustrative exercise, not a client result.
Verify more than the headline total
Repeat the reconciliation using the saved working copies, then check a new controlled refresh. Compare identifiers, amounts and exceptions rather than only the summary card. Matching totals can hide one overstated order and another understated order.
On November 4, 2026, we ran 15 checks on synthetic data using Python 3.12.14 and Decimal; all 15 matched their expected results. They cover repeated order values after a join, status and period filters, a missing order, offsetting amount differences, an older extract and distinct counts across categories. This checks the example’s calculations, not Power BI or its DAX engine, a SQL database, a connector or an actual refresh. Access permissions, export limits and time-zone conversions were not tested. The reference register is assumed complete for this exercise.
For your own report, include at least a period with no records, two orders with equal amounts, an order with multiple detail rows, an excluded record and a missing value. For a ratio, check the numerator and denominator separately; do not replace missing data with an invented percentage. Set rounding and tolerance rules to suit the metric, rather than adding them afterwards to hide a discrepancy.
Make the correction in a report copy or test environment first. Keep the previous version, have the business owner review the reconciliation and check other visuals that share the model. Add these cases to your user acceptance tests. If the source itself is incomplete or the required history does not exist, record that limit instead of manufacturing an expected total.
Checklist before approving the metric
- Both sides use the same definition, population, date, currency and level of detail.
- Exports cover the authorized scope, with known reference times.
- Missing, unexpected and repeated identifiers are explained.
- Amounts match by record; offsetting errors have been checked.
- The correction has been reviewed, rerun and validated across affected visuals.
If this reconciliation returns every month, our custom applications and integrations service in Quebec and Canada can help automate the useful checks. A metric definition and an anonymized example of a discrepancy provide a concrete starting point. For exchanges between systems, our ERP and CRM integration guide also addresses which source owns each piece of data.