How do you calculate OEE in Power BI?

Build separate measures for availability, performance and quality from summed minutes and counts, then multiply them. Availability is run time over planned time, performance is ideal cycle time multiplied by total count over run time, and quality is good count over total count. Avoid averaging OEE percentages, which gives every machine equal weight regardless of volume.

Do I need a data gateway for factory data?

Yes, if the data sits on a SQL Server, file share or folder inside your plant network. The on-premises data gateway lets the Power BI service refresh that data on a schedule. Install it on an always-on server, keep it updated, and make one named person responsible for it so refreshes do not quietly stop.

How often can Power BI refresh a factory dashboard?

With Power BI Pro, up to eight scheduled refreshes a day. With Premium, Premium Per User or Fabric capacity, up to 48. Set the refresh time zone to Malaysia time and time a refresh before each shift handover. For live machine data, look at Fabric Real-Time Intelligence rather than ever faster scheduled refreshes.

Can we build these dashboards from Excel shift logs?

Yes, and many plants start that way. Keep the logs in one SharePoint or network folder with a fixed template and a short list of reason codes, then load the whole folder through Power Query. Move the data to a database later, when volumes grow or several lines need the same figures.

Which manufacturing dashboard should we build first?

Usually OEE, because it shows where capacity is lost and forces you to clean up downtime records and production counts. Those same tables then feed the scrap and maintenance dashboards, so each later build goes faster and the numbers agree across reports.

Power BI Manufacturing Dashboards: Five to Build First in Malaysia

Start with five Power BI dashboards: OEE, scrap and rework, on-time delivery, maintenance, and inventory. Each one answers a question plant managers already ask every morning, and each can be built from data most factories already hold in ERP exports, Excel shift logs and SQL Server databases. Build them in that order, prove the numbers against manual figures, then automate refresh.

At a glance

Dashboard	Core question	Minimum data
OEE	How much good output did we lose?	Shift times, downtime log, counts, ideal cycle time
Scrap and rework	Where do defects come from?	Output counts, defect log with reason codes
On-time delivery	Did we ship what we promised?	Order lines, promised dates, shipment dates
Maintenance	Which machines fail, and how fast do we fix them?	Work orders, downtime start and end times
Inventory	Too much, too little or too old?	Stock snapshots, movements, item master

Before you build: get the data foundation right

Most factory dashboards fail on data, not visuals. Sort out four things first.

Know where each number lives

Typical sources in a Malaysian plant are the ERP (SAP, Dynamics 365 or a local package), Excel logs kept by supervisors on a shared drive, and SQL Server databases behind the ERP or a manufacturing execution system. Machine data from PLCs and SCADA can come later. List each source, its owner, and how often it changes.

Model it as a star schema

Microsoft's guidance on star schema for Power BI is the single most useful page for this work. Keep events in fact tables (production runs, downtime, defects, shipments, stock movements) and descriptions in dimension tables (date, shift, machine or line, product, reason code, customer). Load each fact table at one consistent grain, such as one row per machine per shift.

Build a plant calendar, not just a date table

Malaysian public holidays are not the same in every state, so a plant in Penang and a plant in Johor have different working days. Give each plant its own calendar with shifts, planned breaks and holidays. That calendar defines planned production time, which every OEE figure depends on. Decide early how to handle night shifts that cross midnight: most plants assign the output to the date the shift started.

Clean up reason codes

Free-text downtime and defect reasons ("m/c rosak", "machine breakdown", "BD") make Pareto charts useless. Agree a short list of codes with production and quality, and enforce it in the log template or data entry form.

Dashboard 1: OEE

Overall equipment effectiveness combines the three measures that Microsoft's Azure IoT Operations overview lists for equipment effectiveness: availability, performance and quality. The usual formula is:

Availability = run time divided by planned production time
Performance = (ideal cycle time multiplied by total count) divided by run time
Quality = good count divided by total count
OEE = availability multiplied by performance multiplied by quality

In DAX, build each part as a measure:

Planned Minutes = SUM ( Production[PlannedMinutes] )
Downtime Minutes = SUM ( Downtime[Minutes] )
Run Minutes = [Planned Minutes] - [Downtime Minutes]
Availability = DIVIDE ( [Run Minutes], [Planned Minutes] )
Performance =
    DIVIDE (
        SUMX ( Production, Production[IdealCycleMinutes] * Production[TotalCount] ),
        [Run Minutes]
    )
Quality = DIVIDE ( SUM ( Production[GoodCount] ), SUM ( Production[TotalCount] ) )
OEE = [Availability] * [Performance] * [Quality]

Two traps to avoid. First, never average OEE percentages across machines or shifts; these measures sum the minutes and counts first, which weights each machine correctly. Second, agree what counts as planned time. Scheduled breaks and planned maintenance usually come out of planned time, while changeovers usually count as downtime. Write the rule down and put it in the report's tooltip.

Useful visuals: OEE trend by line and shift, the three components side by side so people see which one is dragging, and a downtime Pareto by reason code.

Dashboard 2: Scrap and rework

Scrap tells you what you threw away. Rework tells you what you paid to fix. Track both.

Business Central's Power BI Manufacturing app, for example, defines scrap percentage as scrap quantity divided by output quantity. Some plants divide by output plus scrap instead. Either is fine if you label it and never mix the two.

Scrap % = DIVIDE ( SUM ( Quality[ScrapQty] ), SUM ( Production[OutputQty] ) )
First Pass Yield = DIVIDE ( SUM ( Production[GoodFirstTime] ), SUM ( Production[TotalCount] ) )
Scrap Cost = SUMX ( Quality, Quality[ScrapQty] * RELATED ( Product[StandardCost] ) )

The cost measure is what gets management attention: a small scrap rate on an expensive part can cost more than a high rate on a cheap one. Slice by defect reason, product, machine, shift and supplier lot. When scrap rises on one shift only, the cause is usually a setting, a material batch or training, and the dashboard should make that visible within a day.

Dashboard 3: On-time delivery

Customers judge you on this one. Define it before you build it:

On time: the line shipped on or before the promised date. Decide whether "promised" means the customer's requested date or your confirmed date, and say so on the report.
In full: the shipped quantity covers the ordered quantity.
Partial shipments: use the last shipment date for each order line.

The simplest model adds an on-time flag in Power Query, then counts it:

Shipped Lines = CALCULATE ( COUNTROWS ( OrderLine ), NOT ISBLANK ( OrderLine[LastShipDate] ) )
On Time Lines = CALCULATE ( COUNTROWS ( OrderLine ), OrderLine[OnTimeFlag] = 1 )
On Time % = DIVIDE ( [On Time Lines], [Shipped Lines] )

Order lines have several dates: order date, promised date and ship date. The star schema guidance calls this a role-playing dimension. Either create separate date tables for each role, or keep one active relationship and use USERELATIONSHIP in measures for the others.

Dashboard 4: Maintenance

Maintenance teams need to see which machines fail most, how long repairs take, and whether planned work is keeping up. The definitions used in Dynamics 365 Asset Management KPIs are a sound reference:

Availability %: uptime divided by total time
MTBF (mean time between failures): total time divided by the number of faults
Mean repair time: repair time divided by the number of faults

Failures = CALCULATE ( COUNTROWS ( WorkOrder ), WorkOrder[Type] = "Breakdown" )
Mean Repair Hours = DIVIDE ( SUM ( WorkOrder[RepairHours] ), [Failures] )
MTBF Hours = DIVIDE ( [Operating Hours], [Failures] )

Here, Operating Hours is a measure built from the plant calendar for the machines in context. Show MTBF and mean repair time by machine, the backlog of open work orders by age, and the split between planned and breakdown work. Use the same downtime log as the OEE dashboard. If maintenance and production keep separate downtime records, the two dashboards will disagree and both will lose credibility.

Dashboard 5: Inventory

Inventory ties up cash, and manufacturers usually hold three kinds: raw materials, work in progress and finished goods. The questions are how much stock you have, how many days it covers, and which items have not moved.

Stock balances are snapshots, so they must not be summed across dates. Take the closing balance on the last date that has data:

Closing Stock Value =
    CALCULATE (
        SUM ( StockSnapshot[Value] ),
        LASTNONBLANK ( 'Date'[Date], CALCULATE ( COUNTROWS ( StockSnapshot ) ) )
    )
Days of Cover = DIVIDE ( [Closing Stock Qty], [Average Daily Usage] )

Add ageing buckets based on each item's last movement date, a list of items below safety stock, and a slow-moving list for the monthly stock review. Finance will want this dashboard too, so agree the costing method with them.

Rolling it out on the shop floor

A dashboard nobody trusts is worse than no dashboard. A rollout pattern that works:

Pilot one line. Run the dashboard alongside the existing manual report for a few weeks and explain every difference.
Sign off definitions. Production, quality, maintenance and finance agree each formula in writing.
Automate refresh properly. Data on SQL Server, file shares or local folders needs an on-premises data gateway. Install it on an always-on server, not a supervisor's laptop.
Schedule refresh around shifts. Configure scheduled refresh with the time zone set to Malaysia time (UTC+8). Power BI Pro allows up to 8 scheduled refreshes a day, and Premium, Premium Per User or Fabric capacity allows up to 48. Time one refresh just before each shift handover meeting.
Secure by plant. Use row-level security so each plant sees its own data, while management sees all of it.
Put it where people look. A TV screen on the floor, the Power BI mobile app for supervisors, and a short weekly review that starts from the dashboard.

When the basics are stable, real-time machine data is the next step. Microsoft's connected factory reference architecture shows how Fabric Real-Time Intelligence feeds live equipment data into Power BI, and our Power BI to Fabric migration guide covers the move. If your team still runs these numbers in spreadsheets, read when to move from Excel to Power BI first.

How CertTulen Academy can help

The analysts who build these dashboards need solid data modelling and DAX, which is the core of Microsoft Power BI Data Analyst (PL-300). Engineers who pull data from ERP and MES databases will also benefit from Microsoft SQL Server Fundamentals. CertTulen Academy runs both live and MCT-led with hands-on labs, online or as a private cohort at your plant. CertTulen is an HRD Corp accredited training provider, and many of its professional courses can be claimed by HRD Corp registered employers, so ask us which courses are claimable. Contact us to plan a session.