Store planned minutes, stop minutes, total count, good count and ideal cycle time per line, shift and product. Then write OEE as a ratio of sums: OEE = DIVIDE(SUMX(good x ideal cycle), SUM(planned)). Never average percentages, since a ratio of sums stays correct at every level, from one shift to the whole plant.
What you are building
This is a report for supervisors and managers: OEE with its factors, a trend, a line comparison and a stop Pareto. For which view suits which audience, see OEE dashboard examples.
We checked each DAX line against Microsoft's function reference and the arithmetic in a script, but we have not run these measures in Power BI Desktop. Step 4 tells you exactly what to test before you trust them.
Step 1: table layout
Use a star schema, which Microsoft's guidance describes as dimension tables for filtering and grouping, and fact tables for summarizing. It also asks that facts load at a consistent grain.
| Table | One row per | Columns |
|---|---|---|
| fProduction | Line, shift, product run | ShiftDate, ShiftKey, LineKey, ProductKey, PlannedMin, StopMin, TotalCount, GoodCount, IdealCycleSec |
| fStops | Stop event | ShiftDate, ShiftKey, LineKey, ProductKey, ReasonKey, StopMin |
| dLine, dShift, dProduct, dReason | One member | Key plus names for grouping |
| dDate | Calendar day | Date, Year, Month, ISO Week |
- IdealCycleSec lives on the fact row. When you accredit a faster cycle, last month's OEE keeps the ideal it had.
- ShiftDate, not the clock date. A night shift that crosses midnight must stay in one day, and the column must have the Date type.
- Split minutes between runs. With two products in a shift, each run gets its share of planned and stop minutes, so the rows add up to the shift's true total.
- PlannedMin excludes what your site excludes, such as planned breaks. See how to count breaks.
Step 2: date table
In Power BI Desktop, choose New table on the Modeling tab and paste this. Then mark it through Mark as date table, as the docs require for classic time intelligence functions.
dDate =
ADDCOLUMNS (
CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2027, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "yyyy-mm" ),
"ISO Week", WEEKNUM ( [Date], 21 )
)
WEEKNUM with return type 21 is the ISO 8601 week, so 1 January 2027 falls in week 53 while Year says 2027. Extend the end date before your data reaches it.
Relate dDate[Date] to ShiftDate in both fact tables, one to many, single direction.
Step 3: the measures
Create these as explicit measures. Everything is in minutes, so the losses add up exactly.
Planned Min = SUM ( fProduction[PlannedMin] )
Stop Min = SUM ( fProduction[StopMin] )
Run Min = [Planned Min] - [Stop Min]
Ideal Min =
SUMX (
fProduction,
fProduction[TotalCount] * fProduction[IdealCycleSec] / 60
)
Good Min =
SUMX (
fProduction,
fProduction[GoodCount] * fProduction[IdealCycleSec] / 60
)
Availability = DIVIDE ( [Run Min], [Planned Min] )
Performance = DIVIDE ( [Ideal Min], [Run Min] )
Quality = DIVIDE ( [Good Min], [Ideal Min] )
OEE = DIVIDE ( [Good Min], [Planned Min] )
Speed Loss Min = [Run Min] - [Ideal Min]
Scrap Loss Min = [Ideal Min] - [Good Min]
SUMX is needed because each row has its own cycle time. DIVIDE returns BLANK when the denominator is zero or BLANK, so visuals skip empty groups.
Quality here is weighted by ideal minutes, so Availability x Performance x Quality always equals OEE, at any filter level. Set all four as percentages under Measure tools.
Rolling trend and data checks
OEE 7 Day =
IF (
[Planned Min] > 0,
CALCULATE (
[OEE],
DATESINPERIOD ( dDate[Date], MAX ( dDate[Date] ), -7, DAY )
)
)
Logged Stop Min = SUM ( fStops[StopMin] )
Unexplained Stop Min = [Stop Min] - [Logged Stop Min]
Check Ideal =
IF ( [Performance] > 1, "Ideal too low, check it" )
Use OEE 7 Day on a date axis. The IF stops the line running on past your last production day, and with no date in context MAX lands on the last day of dDate, so a plain card stays blank.
A performance above 100% is a data signal, so don't hide it with a cap in the measure. Performance over 100% lists the usual causes.
Pareto measures
Stop Rank =
IF (
ISINSCOPE ( dReason[Reason] ),
RANKX (
ALLSELECTED ( dReason[Reason] ),
[Logged Stop Min], , , Dense
)
)
Stop Cumulative % =
VAR ThisMin = [Logged Stop Min]
VAR AllMin =
CALCULATE ( [Logged Stop Min], ALLSELECTED ( dReason[Reason] ) )
VAR RunningMin =
SUMX (
FILTER (
ALLSELECTED ( dReason[Reason] ),
[Logged Stop Min] >= ThisMin
),
[Logged Stop Min]
)
RETURN
IF (
ISINSCOPE ( dReason[Reason] ) && NOT ISBLANK ( ThisMin ),
DIVIDE ( RunningMin, AllMin )
)
Reasons tied on minutes share one cumulative value, which is fine for a chart. If the list gets long, filter the visual to Stop Rank of 10 or less, and see how to read a downtime Pareto.
The RANKX docs warn that comparisons on Decimal Number values can misbehave. If ranks look odd, store StopMin as Fixed Decimal Number or round it.
Step 4: test it with numbers you know
Before you build a single visual, load these two rows and compare, because we did not run this in Power BI. If the totals do not match, the model or a measure is wrong.
| Line | Planned | Stops | Total | Good | Cycle s | A | P | Q | OEE |
|---|---|---|---|---|---|---|---|---|---|
| A | 450 | 60 | 7,000 | 6,860 | 3 | 86.7% | 89.7% | 98.0% | 76.2% |
| B | 225 | 90 | 1,500 | 1,350 | 4 | 60.0% | 74.1% | 90.0% | 40.0% |
| Total | 675 | 150 | 77.8% | 85.7% | 96.2% | 64.1% |
The two wrong answers are easy to write by accident. A tile using Average on a stored OEE column, or AVERAGEX ( VALUES ( dLine[Line] ), [OEE] ), gives 58.1%, and how to average OEE explains why.
What to test, line by line
- Totals: with no filter, the matrix must show the table above, with a total of A 77.8%, P 85.7%, Q 96.2% and OEE 64.1%.
- Rolling window: date line A 1 June 2026 and line B 3 June 2026. On 3 June, OEE reads 40.0% and OEE 7 Day reads 64.1%, and 2 June stays blank.
- Pareto: load 3 rows into fStops: A Jam 40, A No parts 20, B Jam 70. Expect Jam 110 min, rank 1, 84.6%, then No parts 20 min, rank 2, 100%.
- Data check: Unexplained Stop Min must read 150 - 130 = 20.
- Slicer: pick Jam in a reason slicer. Logged Stop Min drops to 110, and OEE must stay at 64.1%.
Step 5: the report page
- Card visuals: OEE, Availability, Performance, Quality.
- Line chart: OEE and OEE 7 Day by date, with a slicer for line and shift.
- Matrix: line by A, P, Q, OEE, with Check Ideal as a column.
- Line and clustered column chart: dReason[Reason], with Logged Stop Min as columns and Stop Cumulative % as the line.
- 100% stacked bar: Good Min, Stop Min, Speed Loss Min and Scrap Loss Min per line. These four add up to Planned Min.
If Unexplained Stop Min is large, the Pareto describes only part of the problem. Unknown downtime shows how to close that gap.
Refresh: know the limits
Power BI is a good fit for shift and weekly review, and a weaker one for a screen at the line. Scheduled refresh allows up to 8 daily time slots on shared capacity and 48 on Premium.
Automatic page refresh does not work in Import mode, so it needs a source such as DirectQuery, with a 30-minute minimum on shared capacity. Dedicated capacity can go much lower, subject to the admin's settings, so check yours.
For a live board, see the Grafana and Node-RED build, or the hour-by-hour board for a low-tech version.
Save your own template
Once it works, choose File > Export > Power BI template. The .pbit file keeps pages, model, measures and queries, but not the data.
That is the "OEE Power BI template" you want: built on your own tables and rules. A downloaded one still needs your data shaped like its tables, and you should test its measures with the numbers above.
A report is only as good as the stops and counts feeding it. Fabrico is a light MES for OEE that takes them live from PLCs or IoT sensors, with add-on AI cameras counting output where a machine has no usable signal, with operators tagging the cause at the line. See OEE per line and shift in Fabrico.
What people ask next
How do I calculate OEE in Power BI?
Use measures, not calculated columns. Write OEE as DIVIDE of total good ideal minutes by total planned minutes, so it stays correct at any level. Test your source numbers first with the OEE calculator.
Why does my Power BI OEE not match Excel?
Usually one of them averages percentages, or uses a different time base. Compare planned minutes first, then good ideal minutes, and see common OEE calculation mistakes.
Is there a free OEE Power BI template?
We do not offer a .pbit file here, because yours needs to match your own tables and rules. The measures above can be pasted in, then saved with File, Export, Power BI template. For a simpler start, the OEE tracking template is a sheet.
Can Power BI show live OEE?
Close to it, with DirectQuery and automatic page refresh, subject to capacity limits. For a screen at the line, the Grafana build is usually a better match.
Should I connect Power BI straight to the PLC?
No. Collect signals into a database first, then report from that, as described in OEE data collection methods. PLC signals for OEE lists what to capture.
Fabrico is a light MES for OEE. It takes stops, speed and counts from PLCs or IoT sensors, with add-on AI cameras counting output where a machine has no usable signal, and operators tag the cause at the line.