Reconciliation compares equivalent populations at equivalent cutoffs, then explains every material difference through a documented bridge. Freeze both results, align definitions, compare counts and amounts at the lowest useful grain, and test joins and transformations. Never force agreement by adding an unexplained adjustment or changing filters until numbers happen to match.
Who this is for: Analysts investigating why a dashboard disagrees with finance, operations, or another authoritative reporting system.
- Freeze evidence and align scope, cutoff, timezone, currency, status, and grain before comparing totals.
- Use staged control totals and set differences to locate where records enter, leave, duplicate, or change value.
- Resolve each variance with an owner and durable rule, then automate the control for future refreshes.
Make the comparison equivalent
Capture dashboard filters, refresh timestamp, model version, and displayed value. Export or query the corresponding source result at a fixed cutoff. Align legal entity, geography, product scope, transaction status, timezone, currency conversion, and whether tax or refunds are included. Most apparent disagreements begin with different questions.
Write the expected relationship. A dashboard can legitimately differ from a general ledger because one reports orders and the other recognized revenue. If both are useful, create a bridge rather than declaring either wrong. Identify the authoritative source for each concept and the accepted timing difference between operational and accounting views.
Trace totals through layers
Record row count, distinct business keys, and control amount at source extract, staging, transformed fact, semantic model, and final filtered view. The first layer where the variance appears narrows the investigation. Compare by day, source system, region, status, or another low-cardinality dimension to isolate concentrated differences.
Use anti-joins or set differences to find keys present on one side only. Check duplicate keys, one-to-many joins, sign changes, unit conversion, null handling, and late-arriving dimensions. At the semantic layer, inspect measure filter context and inactive relationships. At presentation, inspect hidden filters, cross-highlighting, and incomplete current periods.
Build a variance bridge
Start from one control total and add or subtract named categories until reaching the other. Categories might include records after cutoff, pending transactions, excluded internal accounts, currency revaluation, refunds posted later, and confirmed defects. Each category needs a query or record list, not a narrative guess.
Set a materiality threshold appropriate to the decision, but retain small unexplained residuals for follow-up. A small net difference can conceal large offsetting errors, so review gross positive and negative variances separately. Preserve evidence and avoid editing source records merely to satisfy the dashboard.
Close and prevent recurrence
Classify each item as expected timing, definition difference, source defect, transformation defect, or report defect. Assign owner, correction, and effective period. Communicate whether historical values will be restated. Update definitions when the issue revealed genuine ambiguity rather than quietly adopting whichever formula was more familiar.
Turn stable bridge categories into automated reconciliation controls. Alert on counts, amounts, missing keys, and unexplained residuals before users see the dashboard. Review accepted differences periodically because systems and policies change. A signed reconciliation should be reproducible from stored queries and cutoffs, not dependent on one analyst's spreadsheet memory.
Reconcile daily net sales
The commerce dashboard shows $2.14 million for June while the finance extract shows $2.08 million.
- Freeze both June outputs and align entities, local-to-UTC cutoff, currency rates, taxes, order status, cancellations, and refunds.
- Compare distinct order IDs and amounts through raw orders, transformed lines, the semantic model, and dashboard filters.
- Use set differences to identify July-posted refunds, internal test orders, and duplicated lines from a product mapping join.
- Construct a bridge from dashboard sales through each evidenced adjustment to the finance total, exposing any residual.
- Fix the duplicate join, document legitimate timing differences, restate affected periods, and schedule a daily control.
Reconciliation control sheet
Use this sheet to investigate and sign off a reporting variance.
- Frozen evidence: report value, filters, refresh, model version, source extract, cutoff, and owner.
- Alignment: entity, population, status, timezone, currency, taxes, adjustments, units, and grain.
- Layer controls: rows, distinct keys, amount, earliest and latest time, and variance at every stage.
- Bridge: named category, key list, query, gross amount, direction, classification, and residual.
- Closure: root cause, fix, restatement, definition update, automated threshold, approver, and date.
Common mistakes
- Comparing an order dashboard with recognized revenue without first documenting their expected bridge.
- Changing filters repeatedly until totals agree and losing the original evidence needed to explain the variance.
- Accepting a small net residual without checking whether large positive and negative errors offset each other.
Try one
A dashboard has 500 more customers than the CRM but the revenue total agrees. Describe how you would investigate without assuming either system is right.
The investigation freezes both populations, aligns customer entity, active status, cutoff, timezone, merges, and exclusions, then compares distinct identifiers through each model layer. It checks split accounts, missing CRM links, duplicate dimensions, and zero-revenue customers. A good answer builds evidenced variance categories, identifies authority by concept, preserves residuals, and adds a recurring control instead of altering customer counts to match revenue.
Sources
- Microsoft Power BI data sourcesOfficial Microsoft reference for connecting Power BI Desktop to source data.
- Microsoft Power BI star schema guidanceOfficial Microsoft guidance for fact tables, dimension tables, relationships, and model grain.