Blog · Board and management reporting · Supply chain
Build a supply chain dashboard from reconciled order and inventory files. Work through service flags, site comparisons, spreadsheet checks, and the distinction between a report and a dashboard.
A Power BI supply chain dashboard should compare service and inventory using reconciled business files, with the reporting period and each measure's denominator visible. Start with the decision, prepare the calculations in a workbook, and prove the totals before choosing charts.
Suppose a US industrial distributor wants to know which site needs a service review and which holds more inventory relative to recent consumption. Its September 2026 review needs an order-line service measure and a separate inventory coverage measure. Neither measure proves why a site performed differently.
Write the service rule before calculating: an eligible line is due in September, and it qualifies only when the full ordered quantity arrives by its promised date. Canceled lines are excluded under a documented policy. Missing delivery evidence needs review before the result is approved.
Use one row per order line after combining any split-delivery records. For a split line, compare the date cumulative deliveries reached the ordered quantity with the promise date. A shipment-event export cannot simply be counted as order lines.
For a broader preparation checklist, use the supply chain dashboard guide; supply chain dashboard examples covers alternative decision layouts.
These are the distributor's eligible lines. Dates below are in September 2026. The final column is calculated, not supplied by a carrier.
| Order line | Site | Due date | Delivered date | Ordered units | Delivered units | Qualifies |
|---|---|---|---|---|---|---|
| L101 | Cleveland | September 25 | September 25 | 100 | 100 | 1 |
| L102 | Cleveland | September 26 | September 28 | 80 | 80 | 0 |
| L103 | Cleveland | September 27 | September 27 | 120 | 100 | 0 |
| L104 | Reno | September 25 | September 24 | 60 | 60 | 1 |
| L105 | Reno | September 26 | September 26 | 40 | 40 | 1 |
| L106 | Reno | September 27 | September 27 | 100 | 100 | 1 |
Put these columns in A:G on a sheet named Orders, with actual spreadsheet dates in C:D. In G2, enter and fill down:
=IF(OR(C2="",E2="",F2=""),"Check",IF(F2<E2,0,IF(D2="","Check",--(D2<=C2))))
A due but undelivered line with confirmed zero delivery counts as a failure even with a blank delivery date. A complete line without a delivery date needs review; resolve every Check before approving the rate. Do not quietly remove incomplete records from the denominator.
On a separate summary sheet, put a site name in A2. Calculate eligible lines, qualifying lines, and service in B2:D2:
=COUNTIF(Orders!$B$2:$B$7,A2)
=SUMIFS(Orders!$G$2:$G$7,Orders!$B$2:$B$7,A2)
=IF(B2=0,"",C2/B2)
Microsoft's SUMIFS documentation explains summing values that meet specified criteria. Keep all referenced ranges the same size.
| Site | Eligible lines | Qualifying lines | Service | Ordered units | Delivered units |
|---|---|---|---|---|---|
| Cleveland | 3 | 1 | 33.3% | 300 | 280 |
| Reno | 3 | 3 | 100.0% | 200 | 200 |
| Total | 6 | 4 | 66.7% | 500 | 480 |
Service is 4 / 6 = 66.7%, rounded to one decimal place. The unit shortfall is 500 - 480 = 20, all in Cleveland. Late L102 and short L103 explain the service failures without treating a late complete delivery as a quantity shortage.
Use the September 30 closing inventory and September cost of goods sold for the same sites. Both amounts use the company's approved cost basis. This is backward-looking value-based coverage, not a promise about how long each individual part will last.
| Site | Closing inventory (USD) | September COGS (USD) | Period days | Coverage days |
|---|---|---|---|---|
| Cleveland | 180,000 | 120,000 | 30 | 45.0 |
| Reno | 120,000 | 180,000 | 30 | 20.0 |
| Total | 300,000 | 300,000 | 30 | 30.0 |
On an Inventory sheet with those columns in A:E, enter in E2:
=IF(OR(B2="",C2="",C2<=0,D2=""),"",B2/C2*D2)
The company check is 300,000 / 300,000 × 30 = 30.0 days. Reconcile inventory to the approved stock valuation and COGS to the period's cost report. Never attach the entire site inventory balance to each order line and then sum it.
Microsoft's Desktop getting-started guide documents Home > Get Data > Excel, selecting the workbook, choosing tables in Navigator, and selecting Load. Use that route for the approved workbook and compare imported counts with the source tables.
Create a site service comparison, a separate inventory coverage comparison, and an exception table containing the failed line identifiers. Label the service comparison as line based and inventory as a September 30 snapshot. Retain counts beside percentages so the reader sees the size of each site's evidence.
For this approach, prepare approved site summaries in Excel and display them at that same level. Disable percentage totals that would sum or average rates. A company summary should divide the company counts again; do not combine its total row with site rows in a summed visual.
Microsoft distinguishes reports from dashboards: dashboards are single-page canvases in the Power BI service, populated with pinned tiles; Desktop creates reports. Publish the checked report through your company's normal process, then pin the desired report visuals in the service if a dashboard is required. Use the report for filtered detail.
Covirage is not affiliated with Microsoft.
The first action is a Cleveland exception review: one late line and one short line. Its higher inventory coverage does not establish that the missing item was available. Match L103's item to usable stock before recommending a transfer or a purchasing change.
Use Power BI operations dashboard guidance for a wider operating review, and choosing analytics software for supply chain when deciding who will maintain the recurring report.
Bring your order-line export and dated inventory file or Google Sheet to Covirage; it maps the columns, and its tools compute and check the service and inventory comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for supply chain teams for the supporting industry context.
Start with order lines showing promised dates, delivered dates, and quantities, plus inventory balances and consumption for the same sites. Keep shipment events separate from inventory snapshots. Include stable identifiers and the extraction date so someone can reproduce the report.
Yes. Microsoft documents importing worksheet or table data through Power BI Desktop's Excel connection. Prepare a workbook with clearly named tables, consistent dates, and approved calculations first, then check the imported row counts against the workbook.
The total often averages site percentages or counts repeated shipment events as orders. Divide the total qualifying order lines by the total eligible lines. Retain the numerator and denominator so filters and site comparisons can be checked.
Choose a cadence that matches the decision and the availability of approved source exports. Display the extraction time and reporting period. An updated display is useful only when the underlying business files have also been updated and reconciled.