Your dashboard rarely disagrees with finance because the data is broken. It disagrees because the two sides define the metric differently, cut time differently, or compare project revenue to general ledger revenue. KPI reconciliation fixes this with one written definition per metric, tested logic in the lakehouse, and a named owner for every gap.
The meeting goes the same way every time. An operations leader puts a new revenue dashboard on the screen, and the CFO opens the general ledger. The numbers differ by a few percent, and the next 20 minutes go to arguing about whose number is real.
I’ve watched that meeting repeat across more than 30 years of enterprise systems work at IBM, Ernst & Young, Johnson & Johnson and McKesson. The charts are almost never the problem. The definitions underneath them are.
Buying another BI tool won’t fix it. Here’s what does, in the order I work through it.
Why does GL revenue never match project revenue?
The general ledger and the project system answer different questions. The GL exists for close, audit, tax and external reporting. The project system exists so delivery leads can see what was earned, burned and billed this week.
The GL books revenue when it’s recognized at period close. The project system counts hours and milestones the day they happen, often weeks before an invoice goes out. Compare the two without a bridge and they will never tie.
Grain is the second trap. Many charts of accounts carry no territory or service-line segment, so a regional profitability report can’t come from the GL alone. You need a conformed mapping in the silver layer that ties each project to its GL account, entity and period.
Refresh timing is the third. A project feed that updates every night will never tie to a GL that closed last month, so stamp an as-of date on every dashboard tile and compare like periods only.
| Dimension | General ledger revenue | Project (operational) revenue |
|---|---|---|
| Built for | Close, audit, tax and external reporting | Delivery, staffing and margin decisions |
| Recorded when | Recognized at period close under the revenue policy | When hours, milestones or deliverables happen |
| Typical grain | Account, entity, period | Project, client, task, person, week |
| Territory and service line | Often missing from the chart of accounts | Usually tagged at the project level |
| Owner | Controller or CFO | Delivery or operations leader |
| Common source of the gap | Accrual and billing timing at close | Work delivered but not yet invoiced or recognized |
| Use it for | The official number for the board | Steering scope, pricing and staffing mid-project |
Neither ledger is wrong. Publish both, label each one clearly, and keep a reconciliation view that explains every dollar of the gap. Your data governance framework decides which number the board sees.
Why does the same KPI give two different answers?
Take a made-up but typical case. HR reports 12% voluntary turnover and finance reports 18%. Same employees, same separations, same HR system.
The difference is the formula. A rolling 12-month rate divides the last 12 months of separations by average headcount over the same window. An annualized rate scales year-to-date separations up to a full year, so one rough quarter swings it hard.
Both formulas are valid. Showing both to an executive team without labels is how trust dies. Pick one as the official KPI, name the other as a supporting view, and write down why.
Why can’t you treat discrete counts as percentages?
A count is a count: three failed pipeline runs, 14 open tickets, nine new hires. A percentage needs a denominator. If nobody defines it, the rate moves every time the schedule or the headcount changes.
The second mistake is averaging percentages. If one region closes 50% of 10 deals and another closes 20% of 200, the simple average is 35%. The real close rate is about 21%, because you sum the numerators, sum the denominators, then divide.
This is basic Lean Six Sigma measurement discipline. At Analytics AIML, we build it into the Databricks pipeline itself, so the gold layer stores numerators and denominators, not just finished rates.
What goes into a metric dictionary?
Dashboards fail when two departments use the same word for different math. A metric dictionary ends that by deciding each metric once and writing it down. Every entry needs six fields:
- Definition: the metric name and one plain-English sentence on what it measures.
- Formula: the exact SQL logic, including filters and exclusions.
- Source: the approved system and table, and nothing else.
- Time logic: calendar or fiscal, rolling or annualized, and the cutoff time zone.
- Owner: the one business leader who settles disputes.
- Refresh and caveats: how often it updates and what it does not include.
On a lakehouse, the dictionary shouldn’t live in a spreadsheet. Put the logic in governed gold views or Unity Catalog metric views. Then Power BI, Excel and every other tool read the same definition.
What are the six tests to run before any dashboard demo?
Never demo a KPI until it passes a scripted, read-only test set. Read-only matters: the tests query the data and change nothing, so you can run them against production. Here’s a set of six that catches most failures:
- Nulls: missing values follow the written definition instead of dropping silently.
- Duplicates: primary keys are unique, so no join double-counts revenue.
- Date boundaries: period starts and ends match the fiscal calendar and time zone.
- Division by zero: every rate handles an empty denominator.
- Currency and units: conversions use the approved rate table and rate date.
- Tie-out: totals match a known baseline, such as a closed GL period.
Any metric that fails goes straight to the issues list. Our data quality framework shows how to automate these checks inside the pipeline.
How do you triage the open-issues list?
Reconciliation work produces a long list of discrepancies fast. Left alone, that list turns into a vague complaint that “the numbers are wrong.” Put every item into one of four ownership buckets:
- Source system fix: the data is wrong at the source, and the system owner corrects it.
- Data engineering fix: the pipeline logic is wrong, and the data team fixes it.
- Business process change: people record the work inconsistently, and an operations leader changes the process.
- Accepted and documented: the gap is real but expected, like GL timing, and the explanation is written down.
Every item gets one bucket and one named owner. That turns arguments into tasks with due dates.
Can AI keep the numbers reconciled once they match?
Yes, once the definitions are governed, and not before. We don’t sell AI at Analytics AIML. We sell the discipline that makes it pay, and reconciliation is where that line gets tested.
Start with the dictionary itself. A metric dictionary kept in a spreadsheet drifts, because someone changes a filter in one report and nobody updates the sheet. When the definition lives in a Unity Catalog metric view, the change happens once, in code, with version history, and every downstream tool picks it up.
That also makes your definitions readable by AI. Genie, the natural-language interface in Databricks, can answer “what was third-quarter revenue by region?” from the same gold views your dashboards use. Point it at an ungoverned table and it answers just as confidently, with the wrong number.
Then watch the gap itself. The difference between GL revenue and project revenue is a process with its own normal range. Track it by period, set control limits from its history, and flag any period that lands outside them. That is Lean Six Sigma statistical process control applied to the revenue bridge, and it catches a broken join or a missed accrual before the CFO does.
Where should you start with KPI reconciliation?
Start with the five numbers your executive team argues about most, not the whole dashboard. Define them, test them, and reconcile them to the GL before you build another chart.
Here’s the contrarian part: the fix is mostly agreement, and software only holds that agreement in place. A governed lakehouse with written definitions does that job. We run it in the same order every time. Secure. Solve. Scale. Lock down the definitions, close the gaps, then roll the reporting out.
If your dashboards and your GL still disagree, talk to Analytics AIML. Our data engineering services build the governed lakehouse layer, the metric dictionary and the reconciliation views that make one number stick.
Frequently Asked Questions (FAQs)
Why doesn’t my dashboard revenue match the general ledger?
The two systems record revenue at different times for different purposes. Project systems count work when it happens, while the GL records recognized revenue at period close. A reconciliation view that explains the gap line by line settles the argument.
Can AI automate KPI reconciliation and fix BI discrepancies?
Partly. KPI reconciliation ties every dashboard metric back to one agreed definition and one official source. AI can flag gaps that drift outside their normal range and answer questions from governed metric views, but people still have to agree on the definitions first.
Who should own a KPI definition?
One named business leader owns each metric, not IT. The data team implements the logic, and the owner settles disputes about what the number means.
How does Analytics AIML approach KPI reconciliation on a Databricks lakehouse?
We follow Secure. Solve. Scale. First we lock down written definitions and owners. Then we build a silver-layer mapping from projects to GL accounts and a governed gold view for each metric. Unity Catalog lineage shows which source fed every number, so each gap can be traced and closed.
What tests should a KPI pass before an executive demo?
Run six read-only checks: null handling, duplicate keys, date boundaries, division by zero, currency conversion and a tie-out to a known baseline. A metric that fails any of them stays out of the demo.