How to create a management dashboard that supports decisions
A management dashboard makes issues needing attention visible and lets you reach records explaining a result. Every metric therefore needs a definition, period and source. This guide uses a fictional sales funnel to show how to prepare metrics and avoid incorrect conclusions.
By the end, you can:
- Define metrics unambiguously
- Calculate conversions within the same cohort
- Prepare the dashboard and check results
Choose decisions before choosing charts
Start with three questions someone needs to resolve: which requests await a reply, where opportunities accumulate and what work needs a decision this week? Identify who will act and how often they consult the dashboard. Daily operations may need a list; monthly review may need a trend.
Microsoft’s dashboard guidance recommends considering the audience and clearly presenting essential information. Turn that into a design rule: every metric should explain a situation, open relevant records or guide an action. A number without a definition or consequence may occupy space without helping management.
Write a metrics dictionary
Define what counts, the unit, source fields, period, time zone and exclusions. “Contacts” may mean unique people, requests or opportunities. Choose a unit and retain it throughout the calculation. Record definition changes to avoid confusing historical comparisons.
Separate distinct events. A won proposal is not automatically an issued invoice or received payment. When tracking amounts, state the source and treatment of taxes, cancellations and currencies. Without those rules, a value may appear comparable while measuring something else.
| Example metric | Definition | Relevant date |
|---|---|---|
| Eligible contacts | Unique requests admitted to the funnel | Request entry |
| With a proposal | Cohort requests with a proposal sent | First proposal sent |
| Won | Cohort requests with recorded acceptance | Acceptance |
| Awaiting a reply | Open requests awaiting team action | Last action and next action |
Determine what each row represents
Before adding values, confirm granularity: does a row represent a request, proposal, proposal line or activity? Joining proposals with activities may repeat the same amount several times. Counting distinct identifiers resolves some counts, but does not automatically correct duplicated sums.
Microsoft’s star-schema modelling separates events and measures from dimensions used for filtering, such as service or owner. This discipline helps even in a small dashboard: keep stable identifiers, explicit relationships and event dates. Preserve history needed to know when an opportunity changed status.
Calculate conversion within the same intake group
Consider a fictional cohort: 100 eligible requests arriving from 1 to 31 October. By the observation on 17 November, 40 had received proposals and 12 were won. Every win belongs to the group with proposals. Observed conversions are 40 ÷ 100 = 40%, 12 ÷ 40 = 30% and 12 ÷ 100 = 12%.
If the 40 proposals comprise 12 won, 18 lost and 10 open, the rate among closed proposals is 12 ÷ 30 = 40%. It measures something different. The 30% includes proposals still open and does not represent the final probability of winning. Compare cohorts with the same follow-up time.
This guide’s calculator counts unique eligible requests, requests with proposals and won requests from the same cohort. Do not divide this month’s sales by this month’s contacts when sales may originate in earlier months. A snapshot of current statuses does not, by itself, show movement between stages either.
- 100 requests
Defined intake: eligible October requests.
- 40 proposals
40% of requests reached proposal stage by the cut-off.
- 12 wins
30% of proposals and 12% of the cohort, observed up to the cut-off.
Fictional example. Observation date: 17 November. Groups are cumulative and contained within the preceding group.
Organise reading from summary to action
At the top, show priority metrics with their period and update. Then show relevant trends or comparisons. End with records needing action, indicating status, owner and next step. Selecting a metric should open a list respecting the same filters.
Use consistent formats, understandable scales and readable labels. Colour may reinforce a status, but must be accompanied by text. Check mobile reading and keyboard use, especially filters and tables. Show the last update so a synchronisation failure does not appear to mean no activity.
| Question | Useful presentation | Next action |
|---|---|---|
| What is awaiting a reply? | Count and list ordered by waiting time | Open and assign the request |
| Is intake changing? | Time series with comparable periods | Consult source and quality |
| Where do opportunities stall? | Stages and time spent in each | Review cases and reasons |
Check the result before interpreting the trend
Choose some records and follow each from source to metric. Check identifiers, statuses, dates and filters. Compare totals with the authoritative source. Test an empty period, a proposal without an associated contact, a time-zone change and a repeated record.
Distinguish zero from having no basis for calculation. With zero contacts, conversion should show “no data” or “not applicable”. DAX’s DIVIDE function, for example, returns blank by default when the denominator is zero. Define the same behaviour in your chosen technology.
| Error signal | What to check |
|---|---|
| Conversion above 100% | Cohort, unit, duplicates and differing filters. |
| Tripled value | Relationships and totals repeated on detail lines. |
| Sudden drop on one day | Time zone, incomplete import and update. |
| Percentage without records | Division by zero and distinction between blank and zero. |
Make rules visible to dashboard users
Attach a worksheet to each metric and name who maintains its definition. During review, ask whether the team could decide and act. A trend suggests where to investigate; explaining its cause requires cases and context. Add metrics when they address a specific need.
Worksheet for your project
Management metric worksheet Question and decision it should support: Responsible person and consultation frequency: Metric name, unit and definition: What each source row represents: System, table and fields used: Numerator and denominator, where applicable: Cohort, period, time zone and observation date: Filters, exclusions and duplicate handling: Behaviour when no data exists: Update frequency and last update time: List of records explaining the result: Expected action and person responsible: Source reconciliation and test cases: Definition version and approval date:
Read your funnel with clear denominators
Enter figures for the same group of requests, with the same intake period and cut-off date. Initial values are a fictional example.
Calculations run in this browser. Values are neither sent nor saved.
Sources and criteria
Content prepared with AI assistance and research using technical sources, with checks of the relevant examples and calculations. The references support the guide’s decisions; examples identified as fictitious let you try out the method.
