Designing a KPI that reconciles

Nothing destroys trust in a dashboard faster than two numbers that should match and don't. The CEO tab says revenue is $1.79B; the executive tab's segment breakdown adds up to $1.81B; a third panel shows something in between. Each query is individually defensible, and together they make the whole dashboard look broken. Making KPIs reconcile is subtle work, and it's worth walking through the two failure modes we hit — and fixed — building ours.

Failure mode 1: mixing revenue bases

"Revenue" is not one number. There's gross list-level revenue (quantity shipped × unit price) and net merchandise revenue (after discounts and promotions). Both are legitimate; finance recognizes the net figure, merchandising often looks at gross. The bug is using them interchangeably under the same label. Our executive headline was reading a net figure while the breakdown charts summed a gross one — a ~1.3% gap that looked like an error but was really two definitions wearing the same name.

The fix for "these don't match" is rarely a new query. It's deciding which definition is canonical, using it consistently, and labeling the exceptions on purpose.

We anchored every headline and C-level KPI to the net basis, reconciled the CEO and executive revenue to the cent, and — rather than hide the gross breakdowns — surfaced the distinction in a hover tooltip on every chart and on a public methodology page. The 1.3% stopped being a discrepancy and became a documented, on-purpose view: gross before promo, net after.

Failure mode 2: mixing period grains

The second gap was sneakier. Some panels filtered by month (a whole calendar month is in or out); others filtered by exact date (cut off at today). Ask both for "year to date" in the middle of a month and the monthly panels include all of the current month while the daily ones stop partway through it. Same period, different totals — and the difference wanders as the month goes on.

The fix was to make one canonical period definition and derive everything else from it. Period boundaries snap to whole months, and the integer-month filter, the date filter, and the KPI snapshot all read from that single definition. They can't drift because there's only one source of truth for what "year to date" means.

The part most teams skip: guard it

Fixing a reconciliation bug is satisfying and temporary. Six months later someone edits a query, reintroduces a gross figure or a date filter, and the gap silently returns. So the fixes ship with automated tests that assert the invariants: the CEO revenue equals the executive headline exactly; the gross breakdown sits within the known promo gap of net, never below and never by more than a couple percent; and the period boundaries stay month-aligned. If a future change breaks reconciliation, the build fails — not the customer's trust.

The principle

A KPI that reconciles is less about clever SQL and more about discipline: one definition per concept, one source of truth per dimension, exceptions labeled rather than hidden, and the whole thing pinned down by tests. Do that, and "why don't these two numbers match?" stops being a question your dashboard has to answer.