Power BI Reconciliation Report: Compare Two Sets of Totals

A Power BI reconciliation report should compare ledger and transaction totals on the same scope and expose every unresolved difference. Check account-level gaps, their absolute amounts and the documents behind them. Two equal grand totals do not prove the records agree.

Microsoft distinguishes a single-page dashboard from its underlying reports in its Power BI business-user guidance. A headline reconciliation status needs supporting report detail before a finance reviewer can approve it. Covirage is not affiliated with Microsoft or Power BI.

Set the scope of the report review

Write the comparison above the figures: one US entity, September 2026 posting period, USD and the selected revenue and expense accounts. Specify whether amounts are period activity or closing balances. Never compare one source's monthly movement with another source's cumulative balance.

Normalize signs in a review copy while preserving the original columns. This example presents revenue as positive and expenses as negative in both sources. A ledger export that uses debit-positive signs needs a documented conversion before comparison.

Retain the export name, extraction cutoff and source reference with each side. An invoice dated September 30 but posted in October requires a cutoff explanation; moving its date merely to clear the difference changes the question.

For document identifiers, split postings and the full matching method, use financial data reconciliation. This page concentrates on reviewing the account-and-period totals that appear in the report, rather than repeating that broader matching process.

Build a complete account-and-period comparison

Use sheets named Transactions, Ledger and Review. In the first two, put account in A, posting period in B and signed USD amount in C. Scope both exports to the same entity and currency before using these formulas.

On Review, list every account-and-period combination found on either side. A list drawn only from transactions would omit ledger-only accounts. Put account in A, period in B, transaction total in C, ledger total in D, difference in E and absolute difference in F.

Enter these formulas in row 2 and fill down:

C2: =SUMIFS(Transactions!$C$2:$C$10000,Transactions!$A$2:$A$10000,$A2,Transactions!$B$2:$B$10000,$B2)
D2: =SUMIFS(Ledger!$C$2:$C$10000,Ledger!$A$2:$A$10000,$A2,Ledger!$B$2:$B$10000,$B2)
E2: =C2-D2
F2: =ABS(E2)

The labels before the formulas identify destination cells; paste from =. Extend the ranges through the final source row. Microsoft's SUMIFS documentation describes how to sum amounts meeting multiple criteria, which here are account and posting period.

Also preserve source presence. In G2 and H2, count the rows behind each side:

G2: =COUNTIFS(Transactions!$A$2:$A$10000,$A2,Transactions!$B$2:$B$10000,$B2)
H2: =COUNTIFS(Ledger!$A$2:$A$10000,$A2,Ledger!$B$2:$B$10000,$B2)

COUNTIFS counts rows meeting all specified conditions. A zero sum with source rows is different from an absent population. Review G and H before labeling an account matched.

Work an example where the net difference is zero

The scoped transaction and ledger exports produce these grouped September totals. All amounts are exact whole USD. The transaction population includes corresponding source records for both revenue and expense accounts.

The transaction total is 120,000 + 80,000 - 135,000 - 25,000 = $40,000. The ledger total is 118,500 + 82,000 - 136,000 - 24,500 = $40,000. Yet every displayed account has a difference.

The signed bridge is 1,500 - 2,000 + 1,000 - 500 = $0. The gross exceptions are 1,500 + 2,000 + 1,000 + 500 = $5,000. Show both results, along with the four nonzero account groups, so the report cannot appear cleared merely because opposing differences cancel.

With the account groups in rows 2:5, the arithmetic proof is:

=SUM(C2:C5)-SUM(D2:D5)-SUM(E2:E5)

It must return zero. That identity proves the difference column adds correctly; it does not prove the source records are correct. Independently tie C and D to the scoped control totals from their original exports.

Classify exceptions with evidence

The review team investigates the source documents and records these findings. Classification follows investigation rather than being inferred from the size or sign of a difference.

Use categories such as timing, source omission, valuation and amount difference. Add the evidence reference, owner, expected resolution date and reviewer status. An explained item can remain open; an authorized correction should have its own reference.

After corrections or scope changes, retain the original comparison and produce a newly dated review. Show what changed and rerun the source ties. Avoid overwriting an original amount merely to make the report balance.

The financial reporting software guide places this review within the wider reporting process. For a workbook-first investigation of the figures, the Power BI alternatives guide discusses the available approaches.

Where it goes wrong

• Netting away exceptions. Equal grand totals hide the example's $5,000 of gross account differences.
• Using an incomplete group list. Omitting ledger-only accounts removes the very exceptions the report must expose.
• Treating zero as presence. Offsetting postings can sum to zero, while an absent source also returns zero. Preserve row counts.
• Calling a category a resolution. A timing label still needs evidence and an accounting decision.
• Stopping at account totals. Equal amounts within an account can hide mismatched documents. Use the supporting record review when the control requires it.

Review your own reconciliation in Covirage

Bring the scoped ledger and transaction exports with account, period, signed amount and source references in a file or Google Sheet. Covirage maps the columns; its tools compute and check the totals and differences, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account, which takes about a minute, then set up your business and bring a sample of your own data.

See how this supports finance teams.