Multi-site reporting in Power BI starts with the definitions
Multi-site Power BI reporting gives leadership one set of numbers when each figure has a single written definition, a single owner and a single place in the data model where it is calculated. Power BI does not create that agreement. If finance, operations and the sites each build their own report from their own system, the dashboards will disagree however good the visuals are. The work is to settle the definitions first, build the model once, and let every report read from it.
The symptoms are familiar. Revenue for the month is one figure in the finance pack, another in the operations review and a third in the regional manager's spreadsheet. Headcount differs between HR and payroll. A site that closed in March still appears in one report and not another.
This guide is for the CFO, head of operations or business systems manager at an organisation that runs several sites and several systems: an ERP, a CRM, a field service or project tool, payroll, and often a spreadsheet nobody wants to retire. It covers why the numbers disagree, how to agree the definitions, how the Power BI model should be built, and where an AI agent helps and where it should not.
Why the same figure comes out three ways
Disagreement usually has ordinary causes, and each can be traced.
Different moments for the same event
Finance recognises revenue when it invoices. Operations counts it when the job is completed. The CRM counts it when the deal is marked won. All three are correct for their own purpose, and all three differ in any given month.
Different scope
One report includes the acquired business and another does not. One treats a shared service as a site and another allocates it. A site that moved, merged or closed appears under two codes.
Different master data
A customer exists under three names across the CRM, the ERP and the field system. A product is coded differently in each. A site has a cost centre in finance and a different identifier in operations. Joining on these produces gaps and duplicates that nobody notices until a total is questioned.
Different refresh times
A dashboard refreshed on Monday morning and a finance pack closed on Friday evening are looking at different data. Late postings land in one and not the other.
Manual adjustments outside the system
A spreadsheet holds the accruals, the reclassifications or the "management adjustments" that bridge the ERP to the pack. The adjustment is real and sits in nobody's model.
| What each team does | What one definition fixes | Impact | |
|---|---|---|---|
| Revenue | Finance counts at invoice, operations at job completion, sales at deal won | One recognised definition, with the others shown as named bridges | One headline figure |
| Site scope | Acquired and closed sites handled differently in each report | One site list with open and close dates | Same sites everywhere |
| Customer | Three names for the same customer across systems | One customer key mapped from each source | Joins stop dropping rows |
| Data cut-off | Reports refreshed on different days | One published cut-off shown on every page | Comparable periods |
| Adjustments | Held in a personal spreadsheet | Recorded in the model with an owner | Visible and auditable |
Agree ten figures before you build anything
The most useful first step needs no software. Ask the leadership team which figures they actually use to make decisions. The list can be short: revenue, gross margin, cost to serve, backlog or order book, utilisation, debtor days, headcount, safety or quality incidents, and one or two sector-specific measures.
For each one, write down:
- the definition in a sentence a non-accountant can read;
- the source system it comes from and the table or report in it;
- the owner, a named person who answers questions about it;
- the time of day and day of the week it is cut off;
- the known exceptions, and who approves adjustments.
This is a workshop of a few hours with finance and operations in the same room, and it can settle many of the arguments that would otherwise reappear in every dashboard review. Where two definitions are both legitimate, as with revenue at invoice and revenue at completion, the answer is to name both and show the bridge between them, not to pick a winner.
One model, many reports
In Power BI, the place where a figure is defined is the semantic model, the layer of tables, relationships and measures that reports are built on. The pattern that keeps numbers consistent is to build one governed model for the shared figures and have every report connect to it, instead of every report author building their own model from the source system.
When a measure such as gross margin is defined once in the model, a change to the definition flows to every report that uses it. When each report defines it separately, the same figure drifts the first time somebody edits one report and forgets the others.
A few design choices matter more than the visuals:
- A shared site and customer dimension. One table of sites with open and close dates, region and cost centre, and one customer key mapped from each source system. Everything joins to these.
- A date table with the financial calendar. Financial year, period and week definitions in one place, so month-end means the same thing on every page.
- Measures, not columns, for the headline figures. The calculation lives in one named measure with a description.
- Row-level security matched to the organisation. A regional manager sees their region, the leadership team sees everything, from the same model.
- A visible refresh time. Every report shows when the data was last loaded.
Whether the model sits on Power BI alone, or on a wider Microsoft Fabric setup with a lakehouse behind it, is a separate decision that depends on data volumes and how many sources you combine. Data residency and the region the capacity runs in should be confirmed against Microsoft's current documentation and your own agreements before you choose. We design the model so it can move between those options without redefining the figures.
Getting data out of systems that were never designed to talk
The model is only as good as the feeds into it. Many multi-site organisations have an ERP such as Business Central, NetSuite, SAP or Dynamics, plus a CRM, plus an operational system, plus payroll. Each exposes data differently: a database view, an API, a scheduled export or, occasionally, only a report.
Our approach is to bring each source into a staging layer that mirrors the source as it is, then apply the mapping to the shared dimensions in a controlled step. That keeps the raw data available for audit and puts the logic about site codes and customer keys in one place where it can be reviewed.
The mapping tables are where much of the long-term effort sits. A new site, an acquisition or a re-coded product has to be added to the mapping or the numbers drift. Someone has to own it. We recommend it belongs to finance or a business systems lead with a short, written change process, not to whoever built the first dashboard.
For a worked example from one sector, our post on construction project reporting in Power BI shows how cost, progress and safety data reconcile across project systems. For plant data, see how production figures are reconciled across SCADA, historian and ERP.
Where an AI agent helps and where it should not
An AI agent is useful at the edges of this work, where a person would otherwise read two reports and spot the difference by eye.
- Sources refreshERP, CRM, field system and payroll load into the staging layer on the agreed cut-off
- Mapping appliedSite and customer keys mapped to the shared dimensions by documented rules
- Totals comparedThe agent compares headline figures across sources and flags gaps beyond a set tolerance
- Cause proposedUnmapped site, late posting or missing adjustment suggested, with the rows that show it
- Owner reviewsThe named owner of each figure accepts the explanation or corrects the mapping
- Pack publishedReports refresh from the model with the cut-off time and open items shown
Good uses include flagging a customer that appears in the CRM and not in the ERP, a site code with no mapping, a month where finance and operations totals differ by more than an agreed tolerance, and a draft commentary on movements for the finance team to edit.
Poor uses include letting an agent change a definition, post an adjustment or send the pack. Those are decisions with an owner. The agent proposes, and a person approves each one.
Our approach is to run the agent in shadow mode first. For two or three reporting cycles it prepares its reconciliation beside the manual process, your team compares the two, and nothing it produces reaches the pack. Your finance leader sets the accuracy threshold at which it graduates to co-pilot mode.
Which reports to rebuild first
Not every report needs rebuilding at once. A short decision list keeps the first release small.
Where does disagreement cost you the most?
- The monthly leadership or board packStart here: ten agreed figures on one model
- Site manager performance reviewsSecond: add row-level security by site and region
- Customer and contract profitabilitySecond: needs the customer key mapped across systems
- Ad hoc reports built by individual teamsLater: retire them once the model is trusted
Releasing the leadership pack first has a practical benefit. The people who argued about definitions are the ones who now see their agreement reflected, which builds the support needed to retire the older reports.
A phased way to get there
- Phase 1Definitions and baselineAgree ten figures, their owners and cut-offs, and measure today's gaps between systems
- Phase 2Model build and shadow modeBuild the shared model and run the agent's reconciliation beside the manual pack
- Phase 3Co-pilot and handoverThe leadership pack reads from the model, owners approve flagged items, and your IT team takes over
Each phase is a fixed fee agreed before we start, with a decision point where you can proceed, pause or stop and keep what was delivered. The model, the mapping tables and the documentation are built in your own Power BI environment, and the code and credentials are yours at handover.
What to measure
Agree the baseline in Phase 1, from your own reports, so the comparison later is not a matter of opinion.
- Headline figures that differ between two reportsCount this month
- Days from period end to the leadership packMeasure today
- Hours spent each month reconciling reports by handAsk the team
- Reports built outside the shared modelList them
The last measure shows whether the model is being adopted. If teams keep building their own reports, the definitions are not trusted yet and the next step is a conversation with the owners, not another dashboard.
For the wider finance picture, see our guide to multi-entity financial consolidation and how AASB 18 changes the income statement mapping, because a ledger mapped once to the new categories is also the cleanest source for the shared model.
How Solve8 can help
If your leadership meeting starts with an argument about whose number is right, a short review will show which figures disagree, why, and how much of the reconciliation is routine enough for an agent. You leave with a list of your ten headline figures, their candidate definitions and a straight answer on whether a build is worth scoping.
We design and build the model inside your own cloud environment, as part of our custom AI development work, with the feeds from your ERP, CRM and operational systems handled through system integration and the agent reconciliation scoped under process automation. Our professional services work covers reporting across practices and offices. If you are based in Queensland, see how we work with Brisbane organisations. Our principal consultant spent 18 years on projects for BHP, Rio Tinto and Senex Energy, delivered while working with previous consulting employers. These companies are not Solve8 clients. More about how we work is on our about page.
Book a free 30-minute review and bring two reports that disagree. We will tell you what we think is causing the difference and what we would fix first.
Related reading:
- Construction Project Reporting in Power BI: cost, progress and safety reconciled across project systems.
- SCADA, Historian and ERP: Reconciling Production Figures: how plant data is brought into agreement with finance.
- AI Multi-Entity Financial Consolidation for CFOs: consolidating results across entities and ledgers.
- AASB 18 Reporting: Your Ledger Mapped to the New Categories: mapping accounts once for the new income statement.
