Xero Power BI totals don’t match? Reconcile the grain before the DAX

Start with the same accounting question on both sides

A Power BI visual can be internally consistent and still disagree with Xero because it is answering a different question. A financial statement, an invoice register and a payments report do not share the same recognition point or row grain.

Run these six checks before changing the model

1. Organisation: confirm the Xero tenant in Datplan and the organisation used for the source comparison.
2. Period: use the same start/end dates or the same as-at date.
3. Accounting question: decide whether you are checking reported finance, invoices, lines or payments.
4. Filters: remove hidden page, visual, tracking or status filters while reconciling.
5. Relationships: inspect one-to-many joins and any bidirectional paths that can multiply rows.
6. Refresh time: confirm Datplan completed before Power BI reread the files.

Worked example: one invoice, four lines

Assume invoice INV-100 has a header total of £1,000 and four line rows. A model that joins the invoice header to lines and then sums the repeated header amount can show £4,000. The source rows are not missing; the aggregation is at the wrong grain.

Why a Xero P&L can disagree with invoice sales

Invoice activity and financial-statement reporting answer different questions. Timing, document type, tax treatment, account classification, journals and other accounting entries can affect the provider's reported result. Datplan therefore keeps financial-summary reporting separate from invoice facts rather than presenting invoice value as a substitute for reported P&L revenue.

Use a top-down reconciliation trail

1. Record the source total, organisation, period and report/filter settings.
2. Record the Power BI total before changing anything.
3. Break the difference by account, status, tracking category or another relevant dimension.
4. Identify the smallest population containing the difference.
5. Inspect the underlying rows and relationships.
6. Correct the cause, refresh, and repeat the same comparison.

This creates an explainable trail instead of adjusting a measure until the headline happens to match.

Where Datplan fits

Datplan prepares supported Xero reporting grains locally and keeps the source refresh separate from Power BI refresh. It cannot decide the accounting meaning of a custom measure for you. The model still needs a defined question and a source report against which the result can be checked.

See the Xero-to-Power-BI workflow → See the Xero star-schema guide →

Find the layer that changed before you change the calculation

In a file-based workflow there are three places a stale or wrong result can enter: the Xero-to-Datplan pull, the published reporting files, or the Power BI model. Check them in that order. A completed Power BI refresh only proves that Power BI reread whatever files were available; it does not prove those files contain the latest successful Xero pull.

• Source layer: correct organisation, successful pull, expected reporting period and source rows.
• File layer: expected export folder, current file timestamps and unchanged file names.
• Model layer: current Power BI refresh, intended relationships, filters and measures.

This fault-tree approach narrows the investigation before DAX is edited and preserves the distinction between stale data and incorrect modelling.

Status, currency and tracking can create real differences

After the basic grain checks, inspect business filters that legitimately change the population. Draft, authorised, paid, voided or deleted documents may not all belong in the same measure. Tracking-category filters can remove rows from one report but not another. Multi-currency reporting adds another decision: compare source currency, base currency or translated values consistently rather than mixing them in a single total.

Write these settings down with the reconciliation. A total that agrees only when hidden filters happen to match is not a repeatable control.

Keep a small reconciliation record

For a recurring management report, retain the source organisation, source report or population, period/as-at date, Datplan run timestamp, Power BI refresh timestamp, source total, model total and difference. The record does not need to be elaborate. Its purpose is to make the next discrepancy diagnosable rather than rediscovered from scratch.