Field guide · Business intelligence
Define a dashboard metric
Use a small dataset to calculate intake, throughput and backlog, then check the numbers by hand.
Start with a decision
A support manager wants to know whether to assign extra coverage next week. The dashboard should show how many tickets arrived, how many were resolved, and how many remained open at the end of each period.
Download the 10-row practice dataset (CSV). The records are fictional, with one row per ticket. Treat January 1–7 and January 8–14 as two separate reporting windows.
Put the measures side by side
| Period | Opened | Resolved | End backlog |
|---|---|---|---|
| Jan 1–7 | 4 | 2 | 2 |
| Jan 8–14 | 6 | 5 | 3 |
Count intake by creation date and throughput by resolution date. For end backlog, include tickets created on or before the cutoff that have no resolution date or were resolved after the cutoff.
Build and reconcile the report
Import the CSV into Power BI, or load it into a database you can query from Metabase. Use one chart for opened and resolved counts and a separate measure for end backlog. Filter to Billing and trace each result to its ticket IDs before sharing the report.
Check your Billing results
Four Billing tickets appear in the dataset. Two were resolved by January 14 and two remained open. Ticket T03 belongs to the first period for intake and the second for resolution.
For a second exercise, append a duplicate ticket ID. Compare a row count with a distinct ticket count and explain which definition answers the manager’s question.