The sales director’s report says revenue last month was 4.2 million. Finance says 3.9. The board pack says 4.0. All three come from Power BI, all three are “correct”, and the meeting spends forty minutes on which number is real instead of what to do about it.
This is the most common problem we see in Power BI. It is rarely a technical fault. It happens because every report was built separately, and each one quietly decided for itself what “revenue” means. Here is how we fix it, so that every number has one definition and finance is willing to sign it off.
Why the numbers drift apart
Power BI makes it easy to build a report. Someone connects to the data, writes a calculation, and shares the result. The next person builds another report and writes their own version of the same calculation. A few years later there are dozens of reports and several definitions of every important figure:
- Revenue with or without returns, discounts, shipping or tax.
- Revenue by order date, invoice date or payment date.
- Calendar months in one report, the financial year in another.
- One report including a new business unit, another not yet.
- Different currency rates: the rate on the day, the month-end rate, the budget rate.
None of these choices is wrong. The problem is that they are made in many places, by many people, and nobody can see them.
Step 1: write the definitions down, in business language
Before touching Power BI, we agree a metric dictionary with the people who own the numbers. For each important figure it records:
- Name: “Net revenue”, not “Revenue 2 (new)”.
- Definition in plain words: invoiced sales, less credit notes and discounts, excluding tax and shipping.
- Date basis: invoice date, financial calendar.
- Source: which system and which table.
- Owner: the person who decides if the definition changes, usually in finance.
- Check: what it must agree with, for example the sales ledger in the ERP for the same period.
Most companies need 20 to 40 of these, not hundreds. This step takes meetings, not code, and it is where most of the value is.
Step 2: one shared semantic model
In Power BI, the calculations live in a semantic model (previously called a dataset). Instead of every report having its own, we build one shared model, or a small number of them by business area, that holds the agreed measures exactly as the dictionary defines them. Reports then connect to that model and reuse its measures. Nobody writes their own “revenue” any more; they pick “Net revenue” from the list.
A good shared model also includes:
- One date table with your financial calendar, so “this quarter” means the same in every report.
- Clear names and descriptions on every measure, copied from the dictionary, so users see the definition when they hover over it.
- Row-level security where people should only see their own region or team, set once in the model instead of in every report.
- Endorsement. Power BI lets you mark a model as certified. Users can then see which data is the official version.
Step 3: reconcile to the ledger, every month
Finance trusts a number when it agrees with the accounts. So we build the check into the process: each month the key figures in the model are compared with the general ledger or the ERP for the same period. Small differences have a known, written reason, such as timing of the data refresh. Anything else is investigated before the month’s reports go out.
Done consistently for a few months, this changes the conversation. People stop asking whether the number is right and start asking why it moved.
Step 4: control changes
Definitions change: a new product line, a change in how returns are treated, an acquisition. When they do, the change goes through the owner named in the dictionary, is made once in the shared model, and is noted with a date. Where the Power BI licence allows, changes are tested in a separate workspace before they reach the reports everyone uses. Old reports with their own private calculations are retired or moved onto the shared model, one by one.
Step 5: then make the reports good
Only now does report design pay off. With agreed numbers underneath, reports can be shorter and clearer: a few key figures with their trend, and the detail one click away. Different teams can still have their own views, sales by region, finance by entity, operations by site, because the numbers underneath are the same.
Signs your Power BI needs this
- Meetings start with an argument about whose number is right.
- Finance keeps its own spreadsheet “to check” the reports.
- Nobody can say which of several similar reports is the official one.
- Changing one calculation means editing many reports.
- Only one person really understands how the main figures are calculated.
If several of these sound familiar, the fix is usually less work than rebuilding everything. The definitions are agreed first, and the existing reports move onto a shared model one at a time.
We provide Power BI consulting: metric definitions, shared semantic models, reconciliation with your ERP, and reports people actually use. If your figures come from Dynamics 365 Business Central or Finance & Operations, we know those data models well; see our Dynamics 365 services.