ALF College
Reading library

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

10Tickets opened
7Tickets resolved
3Open on Jan 14
Support desk activity by reporting window
PeriodOpenedResolvedEnd backlog
Jan 1–7422
Jan 8–14653

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.

Browse more field guides