Developer ToolsFree generatorPDF, Excel, Word exportDiscrete Manufacturing

OEE Power BI DAX Measures Generator

A common Power BI error is averaging daily OEE percentages, which gives the wrong answer for weeks, months or plants. These DAX measures sum the underlying minutes and counts first, then divide.

Fill in

Production fact table

Planned minutes column

Stop minutes column

Total count column

Good count column

Ideal cycle time column (minutes per unit)

OEE target as a fraction

Generated code

Download:

Base measures

Planned Minutes = SUM('ProductionLog'[PlannedMinutes])
Stop Minutes = SUM('ProductionLog'[StopMinutes])
Run Minutes = [Planned Minutes] - [Stop Minutes]
Total Count = SUM('ProductionLog'[TotalCount])
Good Count = SUM('ProductionLog'[GoodCount])
Ideal Minutes = SUMX('ProductionLog', 'ProductionLog'[IdealCycleMinutes] * 'ProductionLog'[TotalCount])
Good Ideal Minutes = SUMX('ProductionLog', 'ProductionLog'[IdealCycleMinutes] * 'ProductionLog'[GoodCount])

OEE factor measures

Availability = DIVIDE([Run Minutes], [Planned Minutes])
Performance = DIVIDE([Ideal Minutes], [Run Minutes])
Quality = DIVIDE([Good Count], [Total Count])
OEE = [Availability] * [Performance] * [Quality]
OEE Check = DIVIDE([Good Ideal Minutes], [Planned Minutes])

Target comparison

OEE Target = 0.85
OEE Gap = [OEE] - [OEE Target]
OEE Status = IF([OEE] >= [OEE Target], "On target", "Below target")

Measures aggregate numerators and denominators before dividing, which is how weighted OEE should be computed across lines and days. Do not average daily OEE percentages. Rename tables and columns to match your model, and test totals against one known day.

Get a Power BI OEE model reviewed

Optional review of your data model, relationships and OEE measures.

Optional. The tool and every export work without this. No spam.

How to use it

Name your fact table and columns in the form, then copy the measures into your model, using New measure in Power BI Desktop. Add a date table and a line table with relationships to the fact table so slicers work. Put OEE on a card and the three factors on a bar chart to show the loss mix.

One row per line per shift or per day
Store ideal cycle time on the row, or join a standards table
Mark your date table as a date table

Why the aggregation matters

If line A runs 1,000 units at 80% and line B runs 100 units at 95%, a simple average gives 87.5%, but the volume weighted figure is close to 81%. The measures here use the SUM of components, so any slice of data, from one shift to a whole year, reports the correct weighted number.

Reading the results

Show Availability, Performance and Quality side by side so the weakest factor is visible. OEE Check should equal OEE exactly. If it does not, a count or cycle time column has a data issue, such as blank cycle times that drop rows from the SUMX.

Common mistakes

Calculated columns that store OEE per row, then averaged, are the most common cause of wrong totals. Others are missing relationships, text values in numeric columns, and ideal cycle time stored in seconds while stop time is in minutes. Keep the units the same.

Frequently Asked Questions

Why use DIVIDE instead of the slash operator?

DIVIDE returns blank, or an alternate result you choose, when the denominator is zero. The slash operator raises an error in visuals. For shifts with no planned time, DIVIDE keeps your report from breaking.

Can I use this in Excel Power Pivot?

Yes, the same DAX syntax works in Power Pivot data models. Adjust the table and column names and create the measures in the model. The pivot tables will then show the same weighted OEE values.

What if ideal cycle time varies by product?

Store it per row or join it from a product standards table. The SUMX measures multiply cycle time by count row by row, so mixed products are weighted correctly in a single measure.

How should I show the target?

Use a measure for the target, as shown here, so you can change it in one place. For different targets by line, store the target in the line table and use a lookup measure instead.

If you need an OEE dataset that refreshes from your ERP and machines, we can help build the model and the data feed.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Developer Tools tools

See all

Back to All Tools