Business Intelligence / Field notes
Why do two dashboards show different KPIs?
Two reports show different revenue totals. Both refresh successfully. Neither has an obvious formula error. The useful first question is: are they measuring the same thing?
Write the definition before the measure
A KPI needs a small contract: the business event being counted, the unit, the reporting period, the included population and the treatment of reversals. “Monthly revenue” leaves room for invoice dates versus payment dates, gross versus net values, and different currency conventions. These choices can be reasonable individually and still produce incompatible totals.
Know what one row represents
In a star schema, fact tables hold observations and dimension tables support filtering and grouping. A consistent fact-table grain matters: an order and an order line are different observations. Microsoft’s modelling guidance explains this distinction and its role in reliable summarisation.
Microsoft: star schema guidance ↗A small example of a large mistake
Suppose an order is worth €100 and has two line items. Joining the order total onto both lines produces two rows containing €100. Summing that column now gives €200. The arithmetic is correct; the table no longer supports that aggregation. This invented example shows why reconciliation should start with row counts and keys, before adjusting a visual.
Reconcile one slice at a time
Choose one day, one product and a handful of known records. Compare the included IDs, then the amounts, then the aggregation. Check whether both reports use the same date field, status filters and refresh cutoff. Once the small slice agrees, widen the comparison. A total alone rarely explains where the difference begins.
Make the definition easy to find
Place a short definition beside the KPI: unit, scope, reporting date and refresh time. Keep the calculation in a shared measure where appropriate, and maintain a small reconciled example when the logic changes. A dashboard becomes easier to trust when a reader can trace a number back to a clear rule.