The test
A dashboard survives an inspection when an inspector can pick any number, ask where it came from, and get an answer in a minute. Every metric below has a formula, a stated denominator and named sources. Show the formula on the dashboard itself, in small text under each number.
Eight metrics worth tracking
Cases per 1,000 dispensing events
Formula: (confirmed or under-investigation cases in the period ÷ dispensing events in the period) × 1,000. Count events, not doses.
Sources: Case log; dispensing-cabinet or pharmacy transaction history. Refresh: monthly.
Days from flag to investigation start
Formula: median of (date investigation opened − date alert generated), for alerts opened in the period. Also show the oldest unstarted alert.
Sources: Alert system; case log. Refresh: weekly.
Percent of alerts closed with documentation
Formula: alerts closed with a disposition code and a note ÷ all alerts closed in the period.
Sources: Alert system. Refresh: weekly.
Open alerts older than 30 days
Formula: alerts still open 30 or more days after creation ÷ all open alerts, on the last day of the period.
Sources: Alert system. Refresh: daily.
Waste witnessed within policy
Formula: waste events with a witness recorded inside the policy window ÷ all waste events.
Sources: Cabinet waste log; EHR. Refresh: weekly.
Discrepancies resolved within policy time
Formula: cabinet discrepancies resolved inside the policy limit ÷ all discrepancies opened in the period.
Sources: Cabinet discrepancy report. Refresh: weekly.
Reconciled administration records
Formula: removals with a matching administration, return or waste record ÷ all removals. State the matching window.
Sources: Cabinet removals; EHR administration records. Refresh: daily.
Staff reviewed in the period
Formula: staff with controlled-substance access whose activity was reviewed ÷ all staff with such access. Say what "reviewed" means.
Sources: Access list; review log. Refresh: monthly.
Three metrics that mean nothing
- Raw alert count. It rises when you add rules or sites and falls when a feed breaks. With no denominator it says nothing about risk.
- Top users by dispensed quantity. It ranks workload: ICU, anesthesia and ED staff dispense most. Compare people to peers in the same unit and shift, not to the whole hospital.
- Cases confirmed, as a score of the program. A program that looks hard finds more cases. A falling count can mean detection got worse; read it alongside the flag-to-investigation time and the documentation rate.
Setting targets
Do not copy a target from another organisation. Set each one from your own history: take two quarters of the metric before you set a threshold, then set the target as a policy decision with an owner and a date. Examples of the form: "Investigation opened within [N] days of the alert for at least [X] percent of alerts." Record the reason for each target beside it, so an inspector reading the dashboard later can tell a considered threshold from an arbitrary one.
Reading the numbers together
No single metric answers the question "are we finding diversion?". Read them as pairs. A rise in cases per 1,000 dispensing events with a short flag-to-investigation time and a high documentation rate usually means detection is working. The same rise with a long queue of old open alerts means a backlog is building, and the real count is higher than the one shown. A flat or falling case rate, together with a falling share of staff reviewed, means you may be looking less, not finding less.
Before you show it to anyone
- Pick three numbers at random and trace each one to its source records, with a second person watching.
- Check that the dispensing-event count on the dashboard matches the pharmacy or cabinet system's own report for the same dates.
- Confirm that a site whose feed was down shows as a gap and not as a low-risk site.
Build rules
- Show the denominator beside every percentage, and the date range on every view.
- Show missing data as missing: a feed that failed overnight shows a gap, not a zero.
- Keep a log of formula changes with dates, so a figure from last quarter can be reproduced.
- Restrict who sees named-user views, and log each access.
For the technical build (data model, measures and layout) see Power BI for diversion prevention, and for what a vendor should supply, the RFP skeleton.