Power BI for Manufacturing: 5 Reports Every Plant Manager Needs
The dashboard nobody opens
Most manufacturing Power BI projects produce the same artefact: a single dense page with fourteen visuals, a slicer panel down the left, and a title that says "Operations Overview." It gets demoed, it looks impressive, and within a month the plant manager is back to the morning spreadsheet, because the dashboard answers no question they actually have at 6:45 am.
The questions are narrow and repetitive. Which line lost the most time yesterday, and to what. Is scrap on the new part trending or was Tuesday a one-off. Which customer orders will miss this week. What maintenance work is stacked up and what is going to break next. Five questions, five reports. Everything else is decoration. What follows is what each one needs, and the modelling decision underneath it that decides whether the number is trustworthy.
1. OEE by line and shift
Overall Equipment Effectiveness is availability x performance x quality, and every plant already knows the formula. The reporting failure is almost never the arithmetic; it is the aggregation.
OEE does not average. If Line 1 ran 8 hours at 60% and Line 2 ran 2 hours at 90%, the plant OEE is not 75%. You have to recompute the three components from summed base measures at whatever grain the user has sliced to: total run time and planned production time, total good count and total count, ideal cycle time against actual. In DAX this means writing the components as measures over the fact table and dividing at the end, never averaging a stored OEE column. A pre-calculated OEE column in the source table is the single most common reason a plant BI report gets quietly abandoned - it is correct at line-shift grain and wrong everywhere else.
The second requirement is a shift-aware date dimension. A shift running 22:00 to 06:00 belongs to one production day, not two calendar days, and the auto date table will split it. Build an explicit date table plus a shift dimension, assign each event to a production day at ingest, and mark the date table so time intelligence works.
2. Scrap and first-pass yield, as a Pareto
First-pass yield tells you there is a problem. A Pareto by defect code, part, and line tells you where to spend Thursday. The visual is a sorted bar chart of scrap quantity or cost by reason, with a cumulative percentage line, and the whole value of it is that the top three bars are usually 70% of the loss.
This report is entirely dependent on the defect reason being a real dimension. If operators are typing reasons into a free-text field, or picking "Other" because the list has eleven entries and none of them fit, the Pareto is noise. This is the point where reporting work turns into capture work: the reason list has to be maintained as a Dataverse choice column or reference table, owned by quality, and short enough that the right option is on the first screen of the tablet. The capture app is the prerequisite here, not a follow-up, and the build-or-buy question behind it is covered in our field inspection app cost comparison.
Report scrap in cost as well as units. Fifty scrapped washers and fifty scrapped machined housings are not the same conversation, and a costed Pareto reorders itself instantly.
3. Downtime Pareto with a reason hierarchy
Downtime needs its own report, separate from OEE, because the action is different: OEE tells you the line is underperforming, downtime tells you what to fix. Model reason codes as a two-level hierarchy - category (mechanical, changeover, material starvation, operator, planned) and specific reason - so the manager can start at category level and drill.
Two details make this report earn its place. First, split planned from unplanned deliberately; blending them hides a changeover problem inside a maintenance number. Second, report both total minutes and event count. Six four-hour breakdowns and 300 five-minute micro-stops both cost 24 hours, but only one of them is a maintenance problem - the other is a tooling or feed problem, and the frequency column is what reveals it.
4. On-time in-full delivery performance
This is the report the commercial side asks for, and the one that makes the plant manager's case for capacity or tooling investment. OTIF is measured against the confirmed customer date, per line item, and needs both components visible: on-time percentage, in-full percentage, and the intersection. A 96% on-time figure that hides 12% short shipments is a report that will be contradicted by a customer email.
Add a lateness distribution rather than just the percentage. Late orders averaging 1.2 days is a scheduling conversation; a tail of them at 15 days is a different one entirely. The data usually comes from the ERP - Business Central or Finance and Supply Chain - which means a composite model combining the ERP source with Dataverse operational data. Get the shared dimensions right, particularly customer and part, or the two halves of the report will not agree.
5. Maintenance backlog and asset reliability
The fifth report is forward-looking: open work orders by asset and priority, overdue preventive maintenance, mean time between failures by asset, and the assets consuming the most technician hours. This is where a plant that runs Dynamics 365 Field Service has a structural advantage - work orders, assets, bookings, and incident types already sit in Dataverse in a relational shape, so the report is a model over existing tables rather than a new data collection exercise. We covered the operational side of that in a field service implementation for a European manufacturer.
Cross-reference this with the downtime Pareto. An asset that appears in the top three of both is not a maintenance scheduling issue; it is an asset replacement business case, and the two reports together are what funds it.
The model underneath all five
Four decisions determine whether these reports survive their first year.
Use a star schema. Facts for production events, scrap, downtime, orders, and work orders; conformed dimensions for date, shift, line, asset, part, customer, and reason. Flattened wide tables from a source query work for one report and fall apart at the second.
Choose the Dataverse connection mode on volume, not habit. The Dataverse connector with import and incremental refresh partitioned on modifiedon suits most mid-market plants. Once fact tables run to millions of rows, Link to Microsoft Fabric or Azure Synapse Link for Dataverse is the correct path, and it stops analytical queries loading the transactional environment.
Set row-level security early. Plant managers see their own site; group operations sees everything. Retrofitting RLS onto a model with denormalised plant names scattered across fact tables is genuinely painful.
Put the report where the work happens. Embed it in the model-driven app or a Teams channel rather than making people open a portal, and use Power Automate for the exception path - a scrap threshold breach or a third stoppage on one line should push a Teams message to a supervisor rather than wait to be noticed.
Five reports, one model, every number recomputed from base measures. That is a reporting layer a plant manager will actually use at 6:45 am, and building it on your Dataverse and ERP data is what our services and our manufacturing practice are shaped around.
A plant manager needs five reports, not a portal: OEE by line and shift, a scrap and first-pass-yield Pareto, a downtime Pareto with reason codes, OTIF delivery performance, and a maintenance backlog view. Each one lives or dies on the model underneath it - a shift-aware date table, a star schema over Dataverse, and reason codes that are a real dimension rather than free text.
Frequently Asked Questions
Can Power BI calculate OEE correctly across multiple lines?
Yes, but only if OEE is calculated as a DAX measure rather than stored as a column. OEE is availability x performance x quality, and none of those three components averages correctly. If Line 1 runs eight hours at 60% and Line 2 runs two hours at 90%, the plant figure is not 75%. The measure has to sum the base quantities at whatever grain the user has filtered to - run time, planned production time, total count, good count, ideal cycle time - and divide at the end. A pre-calculated OEE column from the source system is correct at its original line-shift grain and wrong at every other level, which is a common reason plant dashboards lose credibility.
Should Power BI connect to Dataverse with import or DirectQuery for manufacturing data?
For most mid-market plants, import mode through the Dataverse connector with incremental refresh partitioned on modifiedon is the right choice: it performs well, keeps DAX simple, and refreshes often enough for shift-level reporting. Move to Link to Microsoft Fabric or Azure Synapse Link for Dataverse when fact tables reach millions of rows, when you need to join Dataverse operational data with ERP data at scale, or when analytical queries are loading the transactional environment. Near-real-time visuals such as a live downtime board are the case for DirectQuery over an analytical store, not for querying Dataverse directly.
How do you handle shifts that cross midnight in a Power BI production report?
Assign each production event to a production day at ingest rather than relying on the calendar date of the timestamp. A shift running 22:00 to 06:00 belongs to a single production day, and a default auto date table will split it across two, which distorts every daily comparison and time intelligence calculation. Build an explicit date dimension marked as a date table, add a separate shift dimension, and store the production day as a column on the fact table so all five reports aggregate on the same definition of a day.
What data do these reports need if the plant is still on spreadsheets?
The capture layer has to come first, and it is usually the larger part of the work. A downtime Pareto is only useful if reason codes are a governed list rather than free text, and a scrap Pareto needs defect codes that operators can actually find on the first screen of a tablet. Power Apps on Dataverse handles that capture - validated at entry, working offline on the plant floor - and Dynamics 365 Field Service already holds work orders, assets, and bookings in a relational shape for the maintenance report. Once the operational data is structured in Dataverse, the five reports are a modelling exercise rather than a data collection project.