Why don't my Power BI numbers match Dynamics 365?
Almost always because the report and the ERP are not counting the same rows the same way: deleted records that were never filtered out, posted versus unposted transactions, a different date or currency rule, a stale refresh, or a measure defined slightly differently from the ERP's own logic. The mismatch is real, it is specific, and it is fixable once you find which of these is biting.
Dynamics-native since 2002·this is the single most common question we are asked, and it always has a specific cause.
What makes a Power BI report disagree with Dynamics?
A Power BI number and a Dynamics number disagree when they are not built from the same rows under the same rules. The report may be including deleted or unposted records the ERP screen excludes, applying a different date or currency rule, reading a stale refresh, or using a measure whose logic drifted from the ERP's. None of these are Power BI being wrong. They are the model underneath making a different, undocumented choice.
The reason this is so common on Dynamics specifically is that the ERP does not hand its data to reporting in a clean, reconciled shape. It exports rows, and the reconciliation, the part that makes the report agree with the ERP, is a modelling job that has to be done deliberately. Where it is skipped or done by a generalist, these mismatches are the result.
The specific reasons, in rough order of how often we see them
- Deleted rows that were never filtered. Dynamics data often retains deleted records and flags them rather than removing them. If the model does not filter the flag, every count and total silently over-states, and nobody notices until a board pack.
- Posted versus unposted transactions. The ERP screen may show only posted figures while the report includes drafts and unposted entries, or the reverse. Same data, different inclusion rule, different number.
- A different date rule. Posting date, document date, due date and modified date are all different, and a report that groups on the wrong one lands transactions in the wrong period.
- Currency and exchange-rate logic. Transaction currency, accounting currency and reporting currency each give a different figure, and multi-entity groups add translation rules on top. A report converting differently from finance will never tie out.
- A stale or partial refresh. The report is correct, it is just old, or the last refresh only loaded part of the data. The number is right for a moment that has passed.
- A measure defined slightly differently. The report's definition of revenue, margin or headcount drifted from the ERP's, often years ago, and now the two answer subtly different questions.
- Row-level security filtering the total. The person running the report can only see part of the data, so their total is genuinely smaller than the unfiltered ERP figure, and both are right for who is looking.
The real problem is usually one layer down
Any one of the causes above can be fixed in isolation, but if they keep recurring, the real problem is that there is no governed, reconciled layer between Dynamics and Power BI. The report is reading raw ERP data and making its own decisions about how to count it, so every new report is a fresh chance to disagree with the last one.
The durable fix is to reconcile once, in a governed model that sits between the ERP and the reporting, so every report draws the same reconciled figure instead of each inventing its own. That is what a data warehouse does, and it is why organisations that keep hitting this mismatch eventually build one.
What to do about it
Start by finding which cause is actually biting, because the fix differs completely between a stale refresh and a drifted measure. A structured diagnosis will tell you whether you have a quick fix or a foundations problem, before you spend on the wrong one.
-
Diagnose.
A reporting health-check traces a disputed number back to source and names exactly which of the causes above is at work. Often it is more than one.
-
Fix the model.
Where the cause is the reporting model, a Power BI build puts the counting rules right so the reports reconcile to the ERP.
-
Reconcile once, underneath.
Where it keeps recurring, the answer is a governed data warehouse layer, so the reconciliation happens once and every report inherits it.
Numbers not matching: common questions
Is Power BI wrong, or is Dynamics wrong?
Usually neither. Both are correctly reporting the rows they were told to count, under the rules they were given. The disagreement is that they were given different rules, most often about which records to include and how to date them. Find the rule that differs and the two reconcile.
Why does it only happen on some reports?
Because each report makes its own counting decisions when there is no shared reconciled layer. One report filters deleted rows and another does not; one uses posting date and another uses document date. Without a governed model in the middle, every report is free to disagree, so only the ones that made a different choice show the gap.
Can we fix this without building a data warehouse?
Often yes, for a specific report. If the cause is a stale refresh, a wrong date rule or a drifted measure, that is a targeted fix. A warehouse becomes the right answer only when the mismatch keeps recurring across reports, because at that point you are fixing the same class of problem over and over instead of reconciling once underneath.
Trace a disputed number back to source
Tell us which figure will not tie out and we will help you find why. A reporting health-check traces a disputed number back to its source and names the cause, so you fix the right thing rather than guessing.
Tell us which Power BI figure will not match Dynamics and we will help you find why.