# Designing an operations dashboard for a distributor

> Which KPIs a distributor should watch daily, weekly and monthly, the design mistakes that get a dashboard ignored and how to set targets and drill-downs.

Source: https://docs.lumina-erp.com/reporting/designing-an-operations-dashboard/

**In short.** Put the numbers someone can act on today on the daily view, trends on the weekly view and financial results on the monthly view. Give every tile one agreed definition, a target, an owner and a drill-down, and cut anything nobody acts on.

An operations dashboard is useful when it changes what someone does today. Most distributor dashboards fail the other way: 30 tiles, numbers that do not match the monthly financials and no one sure what "fill rate" means on this screen. The sections below place each metric on a daily, weekly or monthly view, then cover targets, drill-downs and the common mistakes. The advice holds whatever tool draws the screen.

## Daily, weekly and monthly at a glance

| View | Question it answers | Who acts on it | Typical metrics |
|---|---|---|---|
| Daily | What needs attention before the end of today? | Branch, warehouse, customer service, purchasing | Orders booked, lines shipped, backorders, past-due open orders, receipts waiting, cash received |
| Weekly | Are we trending the right way, and where not? | Operations and sales managers | Fill rate and OTIF trend, gross margin by branch, stockouts on A items, past-due receivables |
| Monthly | Did the business perform, and is working capital healthy? | Leadership and finance | Sales and gross margin versus plan, inventory turns, GMROI, DSO, DIO, DPO, cash conversion cycle, forecast accuracy |

The shorter the cycle, the more the metric should be something a named person can fix in that cycle. A daily view full of monthly ratios invites people to watch numbers they cannot move.

## What decides where a metric goes

### Actionable within the cycle

Past-due open orders can be worked today, so they are daily. Inventory turns move over months, so a daily turns number is noise. If a metric changes meaningfully only once a month, show it monthly, with a trend line.

### Leading or lagging indicator

A **leading indicator** moves before the result. A lagging indicator is the result. For a distributor, backorders, stockouts on A items and open purchase orders past their due date are leading indicators of fill rate and lost sales. Fill rate, OTIF and gross margin are lagging. A daily view should lean on leading indicators, because by the time a lagging number turns, the orders are already late.

| Result you care about (lagging) | Signals that move first (leading) |
|---|---|
| [Fill rate and OTIF](/distribution/fill-rate-and-otif/) | Backorder lines, stockouts on A items, purchase orders past due from suppliers |
| [Gross margin](/distribution/pricing-and-margin/) | Price overrides, sales below a margin floor, cost increases not yet reflected in price |
| [Inventory turns and GMROI](/distribution/inventory-turns-and-gmroi/) | Receipts against forecast, items with no sales in 180 days, excess on-order quantity |
| [DSO and the cash conversion cycle](/distribution/cash-conversion-cycle/) | Past-due receivables by bucket, disputed invoices, credit holds |

### Reconciles to a trusted source

Any metric that finance reports, such as sales, margin or receivables, must tie to the general ledger or the standard ERP report for the same period. If the dashboard shows a different sales figure from the month-end close, readers stop trusting every tile, not only that one. Show those metrics monthly, after close, or label daily figures clearly as preliminary. See [Month-end close for distributors](/erp-projects/month-end-close-for-distributors/) for when numbers become final.

## One definition per metric

Before building a tile, write the definition down and get the owner to agree. For each metric, record:

| Field | Example for line fill rate |
|---|---|
| Name | Line fill rate |
| Formula | Lines shipped complete on first shipment ÷ lines requested |
| Includes and excludes | Excludes drop-ship lines and lines the customer asked to ship later |
| Grain and period | By ship-from branch, by ship date, calendar day |
| Source | The report query or dataset the tile reads, by name |
| Owner | The person who answers for the number |
| Target and thresholds | Target 95%, amber below 93%, red below 90% (invented values) |

Use the formulas on the [KPI formula sheet](/reference/kpi-formulas/) as the starting point, and keep the definitions next to the dashboard where readers can find them. Two tiles called "fill rate" with different formulas on different screens is one of the fastest ways to lose a meeting to arguing about data.

## Targets and thresholds

A number without context cannot be read. Every tile needs something to compare against: a target, last period or the same period last year.

- **Set targets from your own history and plan**, not from an industry figure with a different definition.
- **Use thresholds sparingly.** Green, amber and red on every tile turns the screen into a traffic light nobody reads. Color only the tiles where a breach needs action.
- **Match the threshold to the noise.** A daily fill rate for a small branch swings with a handful of lines. Set a wider band, or show a rolling seven-day figure, so that red means something.
- **Revisit targets on a schedule**, such as each quarter, rather than whenever a number looks bad.

## Drill-down paths

Plan each drill-down path before building, so a reader can go from a red tile to the rows that caused it in two or three clicks.

| Tile | First drill | Second drill | Lands on |
|---|---|---|---|
| Past-due open orders | By branch | By customer | Order lines with promise date and stock status |
| Line fill rate | By branch | By product group | Lines that shipped short, with the reason |
| Gross margin % | By branch | By salesperson or customer | Invoice lines below the margin floor |
| Past-due receivables | By aging bucket | By customer | Open invoices with due date and collector notes |
| Inventory turns | By product group | By item class | Items with the highest on-hand value and no recent sales |

The lowest level should be rows someone can act on: order numbers, item numbers, customers. A drill-down that ends at another summary leaves the reader to rebuild it by hand.

## Refresh cadence

Refresh each view as often as someone acts on it, and no more.

| View | Refresh | Why |
|---|---|---|
| Daily | Several times a day, or near real time for warehouse queues | People act within the day |
| Weekly | Nightly | Trends do not need intraday data |
| Monthly | After the period closes | Numbers must match the financials |

Frequent refresh has a cost on an ERP database, because every refresh runs the underlying queries. Point dashboards at a reporting copy where you can, and follow the read-only and isolation guidance in [T-SQL patterns for ERP reporting](/reporting/tsql-patterns-for-erp-reporting/). Tools also set their own limits. For example, Microsoft documents that Power BI allows up to eight scheduled refreshes a day for a semantic model on shared capacity, and up to 48 on Premium, Premium Per User or Fabric capacity (Microsoft Learn, retrieved 2026-09-28).

Show the refresh time on the dashboard. A reader who does not know the data is from 6 a.m. will chase a problem that was fixed at 9.

## A sample daily operations layout

A single screen for a branch or operations manager. Values are left out on purpose to keep the focus on layout. Most important information goes top left, which follows Microsoft guidance for dashboards read left to right and top to bottom.

| Position | Tile | Shows | Compared with | Drills to |
|---|---|---|---|---|
| Row 1, left | Orders booked today | Count and $ | Same weekday average, last four weeks | Orders by customer |
| Row 1, center | Lines shipped today | Count | Lines due to ship today | Unshipped lines due today |
| Row 1, right | Past-due open orders | Count and $ | Yesterday | Lines by promise date |
| Row 2, left | Line fill rate, rolling 7 days | % | Target | Short-shipped lines with reason |
| Row 2, center | Backorder lines | Count and $ | Last week | Backorders by item, with open PO and due date |
| Row 2, right | A items out of stock | Count | Zero | Items, locations, next receipt |
| Row 3, left | Receipts waiting to put away | Count | Yesterday | Receipts by age |
| Row 3, center | Supplier POs past due | Count and $ | Last week | POs by supplier |
| Row 3, right | Cash received today | $ | Expected collections | Payments by customer |

Nine tiles fit one screen without scrolling. A weekly view can reuse the same grid with trend lines, and a monthly view replaces the operational tiles with financial ones: sales and margin versus plan, turns, GMROI and the [cash conversion cycle](/distribution/cash-conversion-cycle/).

## Choosing the tool

The design rules above do not depend on the tool, and most distributors already own more than one option.

| Option | Good fit when |
|---|---|
| Built-in ERP dashboards (for example Kinetic dashboards built on BAQs, or the reporting and dashboard tools in Prophet 21 software) | Users live in the ERP and the metrics come from one system |
| A BI tool such as Power BI | Metrics combine ERP data with other sources, or leaders want one place for several systems |
| A scheduled report by email | A small audience needs the same view each morning and will not open a dashboard |

Whichever you choose, the query behind each tile should be one reviewed, version-controlled definition. For Kinetic software, see [BAQ fundamentals](/epicor-kinetic/baq-fundamentals/) on getting the grain of a query right.

## Common mistakes

| Mistake | What it looks like | What to do instead |
|---|---|---|
| Vanity metrics | Tiles that always look good and never trigger action, such as total customers ever | Keep a tile only if someone would act when it changes |
| Too many tiles | Two screens of numbers with equal weight | One screen per audience, with the rest in drill-downs |
| Unreconciled numbers | Dashboard sales differ from the income statement | Tie financial metrics to the ledger, or label them preliminary |
| Mixed periods | Month to date next to a rolling 30 days next to last month | One period basis per view, stated on the screen |
| No owner | A red tile everyone sees and no one answers for | Name an owner per metric in the definition |
| No comparison | A number with no target, prior period or trend | Every tile shows what it is measured against |
| Averages that hide problems | Company-wide fill rate at target while one branch is failing | Drill by branch, and consider showing the worst branch |
| Decoration | 3D charts, gauges on every tile, pie charts with many slices | Plain numbers, bars and lines, as Microsoft guidance also advises |

## Next steps

1. List five decisions managers make each day, week and month.
2. Pick one metric per decision and write its definition, owner and target.
3. Build the daily view first, with no more than nine tiles and a drill-down for each.
4. Reconcile every financial tile to the ledger for one closed month before launch.
5. Review after a month: remove tiles nobody used, and add the question people kept asking.

## Sources

- [Tips for designing a great Power BI dashboard (Microsoft Learn)](https://learn.microsoft.com/en-us/power-bi/create-reports/service-dashboards-design-tips)
- [Data refresh in Power BI (Microsoft Learn)](https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data)

---

Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.