← Back to the OEE Tracker

Your OEE Tracker file already contains every day's results, worked out by the tracker. Power BI only reads them: it never changes your file, and you keep entering days in the tracker as usual.

A ready-made Power BI template that does all of this for you is coming soon. Until then, the steps below take about ten minutes.

1. What's in your file

The tracker file (.json) has a table called values: one row for every entered day, line, shift and component, with these columns:

• date, line, shift (empty if you don't use shifts) and version (the formula version that day used)
• component: what the value is, for example breakdown, and value: the number

Values are in base units: times in minutes, quantities in your own units, rates per minute. Availability, Performance, Quality and Loading are always stored under the same fixed names, whatever you called their parts: A.num and A.den (Availability's top and bottom), P.num, P.den, Q.num, Q.den, L.num, L.den.

If you use tags (such as SKU or crop), the file also has a table called entryTags: one row per day, line and shift for each tag, with the tag's name and the value picked that day. Load it with Table.FromRecords(Source[entryTags]) and relate it to your values on date, line and shift to filter by tag.

2. Put the file where Power BI can read it

• Power BI Desktop: any folder on your computer works.
• Power BI service (scheduled refresh online): keep the file in OneDrive or SharePoint, or use an on-premises data gateway.

3. Load the data (Power Query)

1. In Power BI Desktop, choose Get data → Blank query, then Advanced editor.
2. Paste the query below and change the file path on the second line to where your tracker file is.
3. Name the query OEE values and choose Close & apply.

let
    Source = Json.Document(File.Contents("C:\Users\you\Documents\My plant 2026.json")),
    Values = Table.FromRecords(Source[values],
        {"date", "line", "shift", "version", "component", "value"}, MissingField.UseNull),
    Components = Table.FromRecords(Source[valueComponents],
        {"id", "name", "unit"}, MissingField.UseNull),
    Lines = Table.FromRecords(Source[lines], {"id", "name"}, MissingField.UseNull),
    Shifts = if List.IsEmpty(Source[shifts]) then #table({"id", "name"}, {})
        else Table.FromRecords(Source[shifts], {"id", "name"}, MissingField.UseNull),
    AddComponent = Table.ExpandTableColumn(
        Table.NestedJoin(Values, {"component"}, Components, {"id"}, "c", JoinKind.LeftOuter),
        "c", {"name", "unit"}, {"component name", "unit"}),
    AddLine = Table.ExpandTableColumn(
        Table.NestedJoin(AddComponent, {"line"}, Lines, {"id"}, "l", JoinKind.LeftOuter),
        "l", {"name"}, {"line name"}),
    AddShift = Table.ExpandTableColumn(
        Table.NestedJoin(AddLine, {"shift"}, Shifts, {"id"}, "s", JoinKind.LeftOuter),
        "s", {"name"}, {"shift name"}),
    Typed = Table.TransformColumnTypes(AddShift, {
        {"date", type date}, {"value", type number}, {"line", type text}, {"shift", type text},
        {"version", type text}, {"component", type text}, {"component name", type text},
        {"unit", type text}, {"line name", type text}, {"shift name", type text}})
in
    Typed

4. Add the measures (DAX)

Choose Modeling → New measure and add each of these. They divide the totals for whatever you've filtered (a month, a line, a shift), which is how the tracker works out its own figures. Never average daily percentages: a short day would count as much as a full one.

Availability =
DIVIDE (
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "A.num" ),
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "A.den" )
)

Performance =
DIVIDE (
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "P.num" ),
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "P.den" )
)

Quality =
DIVIDE (
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "Q.num" ),
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "Q.den" )
)

Loading =
DIVIDE (
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "L.num" ),
    CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component] = "L.den" )
)

OEE = [Availability] * [Performance] * [Quality]

TEEP = [OEE] * [Loading]

Format the six measures as percentages. To chart one of your own inputs, for example breakdown minutes by month, use a measure such as:

Breakdown minutes =
CALCULATE ( SUM ( 'OEE values'[value] ), 'OEE values'[component name] = "Breakdown" )

Only add up minutes and quantities this way. Rates (such as pieces per minute) and percentages can't be added across days; work them out from totals, as the measures above do.

5. Keep it up to date

• Enter days in the OEE Tracker as usual; it saves them to the file. Then choose Refresh in Power BI.
• Each tracker file holds one year. To compare years, load each year's file with the same query and combine them with Append queries.
• Don't edit the file in Power BI, Excel or a text editor. If it's changed outside the tracker, the tracker warns you and offers to check and recalculate it.

← Back to the OEE Tracker