Author: AILesson10 min setupTested with:ChatGPTReviewed: 2026-08-28
Quick answer
Connect every dashboard number to a decision, population, formula, source, owner, refresh rule, and limitation. Provide: Audience and decisions, Candidate metrics and definitions, Sources and operating constraints. Expected result: A dashboard specification with metric registry, layout, calculations, quality states, and acceptance tests.
1
Add your context
Your text stays in this browser. AILesson Prompts does not send it to a model or server.
2
Your prompt
Unfilled fields remain visible as placeholders, so you can still copy and edit the prompt
Design an Excel dashboard whose metrics are fully defined and auditable.
Users, cadence, questions, decisions, comparisons, targets, actions, and prohibited implications:
[decisions]
Metric names, definitions, numerators, denominators, units, grain, filters, dates, targets, segments, owners, and disputes:
[metrics]
Sources, keys, refresh, history, missingness, access, Excel version, performance, display, and baselines:
[data]
Begin with decisions and remove metrics that do not support a stated question or action. For each retained metric define population, grain, event or status, numerator, denominator, time basis, inclusion/exclusion, unit, aggregation, target direction, comparison, update cadence, source, owner, quality status, and interpretation limit. Resolve or expose conflicting definitions rather than averaging them. Separate current value, target, change, forecast, and annotation. Show counts beside rates and exact dates beside labels such as current or last month. Use visual encodings appropriate to the metric; do not use truncated axes, dual axes, traffic-light color without text, or targets that imply causality. Return: decision-to-metric map; metric registry; source model; calculation plan/formulas; dashboard wireframe; filter interactions; data-quality and stale states; accessible formatting; annotation rules; reconciliation; performance/refresh plan; and acceptance tests using known values.
Private by defaultPrompt assembly happens locally in your browser. Avoid placing confidential information into any AI service unless your organization allows it.
From input to outcome
A worked example
See how concrete context turns this recipe into a usable result
Actual input
Audience and decisions
Weekly support operations review on Monday. Audience: support manager and three team leads. Decide staffing focus and investigation priorities for the previous complete Monday–Sunday week. Questions: incoming eligible cases, backlog at week end, first-response service, reopen pattern, and which queue needs review. Targets are operational thresholds, not proof that staffing caused results. No individual-agent ranking. Show exact period and refreshed-at time. One 1366×768 screen and PDF export.
Candidate metrics and definitions
Candidates: New Cases; End Backlog; FRT SLA%; Reopen%; CSAT; Average Handle Time. Definitions proposed: New Cases = cases created in week excluding spam/test. End Backlog = open eligible cases at Sunday 23:59 UTC. FRT SLA% disagreement: operations uses eligible cases first-responded within plan-specific threshold / eligible cases with first response; quality uses all eligible created cases including unresolved denominator. Reopen% = cases reopened within 7 days / cases closed, but cohort window not settled. CSAT has only 22% response. AHT comes from a different system and is not reconciled. Break down by Queue, not agent. Owners Operations Analytics and Quality.
Sources and operating constraints
Tables Cases one row/case, Events many rows/case, QueueHistory effective-dated, PlanThreshold, Calendar, RefreshLog. Stable CaseID. Data through Sunday 23:59 UTC normally loads Monday 07:00 UTC; current refresh Monday 07:20. Events can arrive 24 hours late. Excel Microsoft 365 with Power Query and Data Model. Baseline known week 17–23 Aug: 1,240 eligible new cases, Sunday backlog 386, 1,150 eligible cases with first response, 972 within threshold. Spam/test 48 excluded. Queue history needed to assign queue at event time. PDF must remain readable without hover or color.
Example output
Decision map: volume and backlog support staffing/investigation; FRT supports service review; queue breakdown locates follow-up. Reopen is Deferred until the closure cohort and full seven-day observation window are defined. CSAT may appear only as “respondent CSAT, response rate 22%, n” in a secondary quality panel, not overall satisfaction. AHT is excluded until sources reconcile.
Metric registry: New Cases—distinct eligible CaseID created 17–23 Aug UTC, excluding spam/test; owner Operations Analytics. End Backlog—distinct eligible CaseID open at 23 Aug 23:59 UTC using status history, not current status. FRT SLA must not launch under one ambiguous label: choose one governed definition or display two explicitly named measures. Under Operations definition, 972/1,150=84.52%, with count “972 of 1,150”; 90 eligible created cases without first response are outside that denominator and must be displayed beside it. The Quality alternative denominator needs the count of all eligible cases to which the threshold can be validly applied; do not assume it is exactly 1,240 without checking exclusions and observation completeness.
Wireframe: header with “17–23 Aug 2026 UTC,” refreshed “24 Aug 07:20 UTC,” and “Events may lag 24h — provisional.” First row: New Cases 1,240, End Backlog 386, FRT SLA 84.52% (972/1,150), No first response 90. Second row: weekly trends with zero-based axes where appropriate and target as a labeled reference line; third row: Queue table with counts, rates, denominator, change, quality flag, and review note. No dual axes or agent ranking. Queue filter applies to every card and visibly changes denominators. PDF uses text status plus pattern/icon, not red/green alone.
Acceptance: source counts reproduce 1,240 and 48 exclusions; backlog uses point-in-time history; FRT reconstructs 972 and 1,150 by plan threshold; queue assignment uses effective date; all rates show n; late events set Provisional; an empty queue shows No data rather than 0%; refresh older than policy shows Stale and suppresses decision wording. Reconcile dashboard totals to metric queries before export.
Why this works
1
A metric registry prevents identical labels from hiding different populations, dates, or denominators.
2
Decision-to-metric mapping limits the dashboard to information someone can interpret and act on.
Check the result
Can every displayed value be recalculated from a named source, formula, date window, and filter state?
Are rates paired with counts and populations, while current, target, change, forecast, and quality remain distinct?
Do stale, missing, conflicting, partial, and inaccessible data produce visible states rather than plausible numbers?
Use it with confidence
Frequently asked questions
Practical answers about when to use this recipe, what to provide, and where human review still matters
What should I prepare before using “Design a dashboard with defined metrics”?
For “Design a dashboard with defined metrics,” prepare Audience and decisions, Candidate metrics and definitions, and Sources and operating constraints. Replace placeholders only with information you can verify. If a detail is unknown, preserve that uncertainty explicitly instead of asking the model to infer it.
When is the “Design a dashboard with defined metrics” result not ready to use?
The result is not ready if it does not yet deliver the stated outcome—A dashboard specification with metric registry, layout, calculations, quality states, and acceptance tests—from the supplied evidence, or if it relies on unresolved assumptions, missing approvals, or invented details. Use the checks as release gates: revise the source inputs or assign a named, authorized reviewer instead of polishing an unsupported output.
Which AI tools have recorded tests for “Design a dashboard with defined metrics”?
The published test record for “Design a dashboard with defined metrics” lists ChatGPT as of 2026-08-28. This confirms recorded runs, not guaranteed compatibility or identical results in later product versions. For another tool or version, keep every constraint visible and repeat the result checks before use.
ChatGPT