A dashboard can show a number clearly and still leave everyone asking what it means.
I’ve seen teams use the same metric name while calculating it differently. One report counts every order placed during the month. Another counts only completed orders. A third uses the date the payment settled. The charts may all be well formatted, but they don’t answer the same question.
Before I build a visual, I want the metric’s definition to be clear enough that another analyst could reproduce it.
Start with the business question
Suppose someone asks for “monthly sales.” That sounds specific, but it leaves important details open:
- Does sales mean orders placed, orders fulfilled or payments received?
- Are cancelled and refunded orders included?
- Is the report grouped by order date or payment date?
- Is the value gross sales or net sales?
- Which currency and time zone apply?
Those choices can change the result. The chart should come after the team agrees which question it is meant to answer.
Write down the calculation
A metric definition should explain its logic in plain language and, where useful, as a calculation.
For example:
Net sales are the value of completed orders, excluding cancelled orders, grouped by the order date.
Then translate that definition into a query:
SELECT
order_date,
SUM(order_amount) AS net_sales
FROM orders
WHERE order_status = 'completed'
GROUP BY order_date
ORDER BY order_date;
This is only an example. A real calculation may also need to account for refunds, discounts, tax, multiple currencies or the table’s grain. The point is to make those rules explicit rather than letting each report author make a different assumption.
Define the denominator for rates
Rates need extra care because the numerator and denominator both affect the result.
For example, “conversion rate” could mean purchases divided by website sessions, leads divided by form submissions, or qualified leads divided by all leads. Those are different metrics, even if a dashboard labels each one “conversion rate.”
I document the exact numerator, denominator, date basis and filters. If a rate is shown as a percentage, I also make sure the dashboard makes the population clear enough for someone to interpret it.
Make filters visible
A metric can change when someone filters by region, product, channel or date. That’s expected, but the user should be able to see which filters are active and whether the metric respects them.
In Power BI, I check that slicers and visual-level filters behave as intended. I also test combinations that users are likely to select. A chart that works for the default view may give confusing results after a filter is applied.
When a metric should ignore a particular filter, that behaviour should be intentional and documented. Otherwise, users may assume that all visuals respond consistently when they do not.
Check the definition against the source data
Once the calculation is written, test it against a small set of records that can be checked manually. I look at a few examples near the boundaries: cancelled orders, refunds, blank dates and records with unusual status values.
This helps catch differences between the written definition and the implementation. It can also reveal that the source data does not contain enough information to calculate the metric as requested.
Give the metric an owner and a change history
Definitions can change as business processes evolve. A team might move from reporting orders placed to reporting completed orders. That may be a sensible change, but historical results can become hard to compare if the change is invisible.
Keep a short metric note with its name, definition, source, filters, owner and last updated date. When the logic changes, record what changed and when. This gives dashboard users context for interpreting trends.
A useful dashboard isn’t just a collection of charts. It is a set of agreed measurements presented in a way people can use. Define the metric first, test the calculation against real records, and then choose the visual that best answers the question.
Top comments (0)