PlantOps BI

Spot the Problem

Each issue is a small chart or table that looks fine and is not. Decide what you would check, then open the answer. Every number is computed from the demo CSVs by a script, and every answer comes with the DAX that surfaces the problem.

Fictional data. Cascadia Industrial Products is a fictional company with synthetic data. Nothing on this page comes from a real plant or company.

Issue 1 of 5

The Plant Looks Fine

Boise OEE, Jan 25 to Jun 25.

A Boise plant OEE trend, one point per month. It stays between 60% and 65% the whole half year, and May 25 is the best month on the chart. The plant manager says the floor is steady.

Nothing on this chart is alarming. What would you check before agreeing?

40%60%80%Jan 25Feb 25Mar 25Apr 25May 25Jun 25Boise plant OEE, Jan 25: 60.1%Boise plant OEE, Feb 25: 60.5%Boise plant OEE, Mar 25: 63.5%Boise plant OEE, Apr 25: 60.9%Boise plant OEE, May 25: 64.2%Boise plant OEE, Jun 25: 63.4%60.1%63.4%Boise plant OEE40%60%80%Feb 25Apr 25Jun 25Boise plant OEE, Jan 25: 60.1%Boise plant OEE, Feb 25: 60.5%Boise plant OEE, Mar 25: 63.5%Boise plant OEE, Apr 25: 60.9%Boise plant OEE, May 25: 64.2%Boise plant OEE, Jun 25: 63.4%60.1%63.4%Boise plant OEE
See the Numbers
PeriodBoise plant OEE
Jan 2560.1%
Feb 2560.5%
Mar 2563.5%
Apr 2560.9%
May 2564.2%
Jun 2563.4%
Show the Answer and the DAX Hide the Answer and the DAX

Break the plant down by line. BOI-03 ran at 41.6% OEE in Feb 25 while the plant showed 60.5%, because the other three lines together held 65.6%. A plant roll-up weights every operating hour equally, so one broken line is diluted by the three that are fine.

Then look at which factor moved. For BOI-03 from Feb to Apr 2025, Availability was 86.3% against 86.9% in other months, but Performance was 49.9% against 62.4%. The line was running, just slowly, which is the kind of loss an uptime view never shows.

Habit worth building: any plant-level KPI card gets a worst-line companion measure next to it.

40%60%80%Jan 25Feb 25Mar 25Apr 25May 25Jun 25Boise plant OEE, Jan 25: 60.1%Boise plant OEE, Feb 25: 60.5%Boise plant OEE, Mar 25: 63.5%Boise plant OEE, Apr 25: 60.9%Boise plant OEE, May 25: 64.2%Boise plant OEE, Jun 25: 63.4%60.1%63.4%BOI-03 OEE, Jan 25: 51.2%BOI-03 OEE, Feb 25: 41.6%BOI-03 OEE, Mar 25: 43.2%BOI-03 OEE, Apr 25: 41.6%BOI-03 OEE, May 25: 53.4%BOI-03 OEE, Jun 25: 52.8%51.2%52.8%Boise plant OEEBOI-03 OEE40%60%80%Feb 25Apr 25Jun 25Boise plant OEE, Jan 25: 60.1%Boise plant OEE, Feb 25: 60.5%Boise plant OEE, Mar 25: 63.5%Boise plant OEE, Apr 25: 60.9%Boise plant OEE, May 25: 64.2%Boise plant OEE, Jun 25: 63.4%60.1%63.4%BOI-03 OEE, Jan 25: 51.2%BOI-03 OEE, Feb 25: 41.6%BOI-03 OEE, Mar 25: 43.2%BOI-03 OEE, Apr 25: 41.6%BOI-03 OEE, May 25: 53.4%BOI-03 OEE, Jun 25: 52.8%51.2%52.8%Boise plant OEEBOI-03 OEE

The DAX

Availability % =
DIVIDE (
    COUNTROWS ( fact_production_hourly ) * 60 - SUM ( fact_production_hourly[downtime_minutes] ),
    COUNTROWS ( fact_production_hourly ) * 60
)

-- Minutes the units would take at ideal rate, summed per rate
Ideal Minutes Produced =
SUMX (
    VALUES ( dim_line[ideal_rate_uph] ),
    DIVIDE (
        CALCULATE ( SUM ( fact_production_hourly[units_produced] ) ) * 60,
        dim_line[ideal_rate_uph]
    )
)

Ideal Minutes Good =
SUMX (
    VALUES ( dim_line[ideal_rate_uph] ),
    DIVIDE (
        CALCULATE (
            SUM ( fact_production_hourly[units_produced] )
                - SUM ( fact_production_hourly[scrap_units] )
        ) * 60,
        dim_line[ideal_rate_uph]
    )
)

Performance % =
DIVIDE (
    [Ideal Minutes Produced],
    COUNTROWS ( fact_production_hourly ) * 60 - SUM ( fact_production_hourly[downtime_minutes] )
)

Quality % = DIVIDE ( [Ideal Minutes Good], [Ideal Minutes Produced] )

-- Equals A x P x Q, and rolls up across lines with different rates
OEE % = DIVIDE ( [Ideal Minutes Good], COUNTROWS ( fact_production_hourly ) * 60 )

-- The check: worst line inside whatever the card is filtered to
Worst Line OEE % =
MINX ( VALUES ( dim_line[line_code] ), [OEE %] )

Issue 2 of 5

The Best Month in Half a Year

On-time shipping by order month, Mar 26 to Aug 26.

On-time shipping by order month: ship date against promise date, because this table has no delivery date. Mar 26 to Jul 26 sit between 94.5% and 95.0%. Aug 26 shows 97.6%, the best bar on the chart, and the data runs through the last day of the month.

Did the improvement program just land, or is something else going on?

85%90%95%100%Mar 26Apr 26May 26Jun 26Jul 26Aug 26Mar 26: 94.8%94.8%100% shippedApr 26: 94.5%94.5%100% shippedMay 26: 94.8%94.8%100% shippedJun 26: 95.0%95.0%100% shippedJul 26: 94.8%94.8%100% shippedAug 26: 97.6%97.6%57% shipped85%90%95%100%Apr 26Jun 26Aug 26Mar 26: 94.8%94.8%Apr 26: 94.5%94.5%100%May 26: 94.8%94.8%Jun 26: 95.0%95.0%100%Jul 26: 94.8%94.8%Aug 26: 97.6%97.6%57%Second row: share of lines shipped
See the Numbers
PeriodValueNote
Mar 2694.8%100% shipped
Apr 2694.5%100% shipped
May 2694.8%100% shipped
Jun 2695.0%100% shipped
Jul 2694.8%100% shipped
Aug 2697.6%57% shipped
Show the Answer and the DAX Hide the Answer and the DAX

Look at how many of those lines have shipped. Of 1,411 order lines placed in Aug 26, only 57% have a ship date. The other 605 are still open and drop out of an average over shipped lines. Lines that shipped quickly are the easy ones, so the month looks better than it is.

Some of the open lines are already past their promise date: 16 of them. Counting those as late moves the month from 97.6% to 95.7%. The rest of the open lines will land later, and the late ones will pull the number down as they ship.

Measuring by promise date instead of order date gives a cleaner read: 1,440 lines were promised in Aug 26, 16 are still open, and on-time shipping with those counted late is 95.0%.

One more limit: every figure here stops at the dock. On-time shipping is not the on-time delivery a customer feels, and the on-time delivery entry lists using ship date as its first mistake. Once a delivery date is loaded, the same open-line check applies, and against the same promise dates the delivery number can only come out equal or lower.

85%90%95%100%Mar 26Apr 26May 26Jun 26Jul 26Aug 26Mar 26: 94.8%94.8%100% shippedApr 26: 94.5%94.5%100% shippedMay 26: 94.8%94.8%100% shippedJun 26: 95.0%95.0%100% shippedJul 26: 94.8%94.8%100% shippedAug 26: 97.6%97.6%95.7%57% shipped85%90%95%100%Apr 26Jun 26Aug 26Mar 26: 94.8%94.8%Apr 26: 94.5%94.5%100%May 26: 94.8%94.8%Jun 26: 95.0%95.0%100%Jul 26: 94.8%94.8%Aug 26: 97.6%97.6%95.7%57%Second row: share of lines shipped

The DAX

On-Time Ship % (shipped only) =
CALCULATE (
    AVERAGE ( fact_orders[on_time] ),
    NOT ISBLANK ( fact_orders[ship_date] )
)

Open Lines = CALCULATE ( COUNTROWS ( fact_orders ), ISBLANK ( fact_orders[ship_date] ) )

Open Past Promise =
VAR AsOf = MAX ( dim_date[date] )
RETURN
    CALCULATE (
        COUNTROWS ( fact_orders ),
        ISBLANK ( fact_orders[ship_date] ),
        fact_orders[promise_date] <= AsOf
    )

-- Counts open lines past promise as late
On-Time Ship % (honest) =
VAR Shipped = CALCULATE ( COUNTROWS ( fact_orders ), NOT ISBLANK ( fact_orders[ship_date] ) )
VAR OnTime  = SUM ( fact_orders[on_time] )
RETURN DIVIDE ( OnTime, Shipped + [Open Past Promise] )

Issue 3 of 5

A Red Card from One Month

Boise Plant TRIR by month, ending Jan 26.

The safety card for Boise Plant shows a TRIR of 15.6 for Jan 26. Someone is about to send an alert to the plant manager.

Is this plant having a safety problem? What would you look at first?

0.05.010.015.020.0Mar 25May 25Jul 25Sep 25Nov 25Jan 26Feb 25: 0.00.0Mar 25: 0.00.0Apr 25: 0.00.0May 25: 0.00.0Jun 25: 8.48.4Jul 25: 0.00.0Aug 25: 0.00.0Sep 25: 0.00.0Oct 25: 0.00.0Nov 25: 0.00.0Dec 25: 0.00.0Jan 26: 15.615.60.05.010.015.020.0Apr 25Jul 25Oct 25Jan 26Feb 25: 0.0Mar 25: 0.0Apr 25: 0.0May 25: 0.0Jun 25: 8.48.4Jul 25: 0.0Aug 25: 0.0Sep 25: 0.0Oct 25: 0.0Nov 25: 0.0Dec 25: 0.0Jan 26: 15.615.6
See the Numbers
PeriodValue
Feb 250.0
Mar 250.0
Apr 250.0
May 250.0
Jun 258.4
Jul 250.0
Aug 250.0
Sep 250.0
Oct 250.0
Nov 250.0
Dec 250.0
Jan 2615.6
Show the Answer and the DAX Hide the Answer and the DAX

Check the numerator and the denominator. TRIR is recordables x 200,000 / hours worked. In Jan 26 the plant had 2 recordables on 25,664 hours. That many incidents on one month of hours produces 15.6, and each further recordable adds another 7.8.

Over the trailing 12 months the same plant has 3 recordables on 301,723 hours, a TRIR of 1.99. Across the full 32 months it is 2.75. In 22 of 32 months the plant logged no recordable at all, so a monthly rate swings between zero and a scary number.

That does not mean ignore the incident. Investigate it. But rate cards for a single plant-month are noise, and the status color should be driven by a rolling window.

0.05.010.015.020.0Mar 25May 25Jul 25Sep 25Nov 25Jan 26Feb 25: 0.00.0Mar 25: 0.00.0Apr 25: 0.00.0May 25: 0.00.0Jun 25: 8.48.4Jul 25: 0.00.0Aug 25: 0.00.0Sep 25: 0.00.0Oct 25: 0.00.0Nov 25: 0.00.0Dec 25: 0.00.0Jan 26: 15.615.6Trailing 12 months: 2.00.05.010.015.020.0Apr 25Jul 25Oct 25Jan 26Feb 25: 0.0Mar 25: 0.0Apr 25: 0.0May 25: 0.0Jun 25: 8.48.4Jul 25: 0.0Aug 25: 0.0Sep 25: 0.0Oct 25: 0.0Nov 25: 0.0Dec 25: 0.0Jan 26: 15.615.6Trailing 12 months: 2.0

The DAX

Recordables =
CALCULATE (
    COUNTROWS ( fact_safety_incidents ),
    fact_safety_incidents[incident_type] = "Recordable"
)

Hours Worked = SUM ( fact_labor_monthly[hours_worked] )

TRIR = DIVIDE ( [Recordables] * 200000, [Hours Worked] )

TRIR T12M =
VAR Window = DATESINPERIOD ( dim_date[date], MIN ( MAX ( dim_date[date] ), TODAY () ), -12, MONTH )
RETURN
    DIVIDE (
        CALCULATE ( [Recordables], Window ) * 200000,
        CALCULATE ( [Hours Worked], Window )
    )

Issue 4 of 5

The Inventory Number Nobody Believes

Inventory value card, all snapshots, through Aug 26.

A card on the inventory page reads $204.2M. The latest month-end on its own comes to $6.9M. The source table holds one row per part, plant and month-end for 32 month-ends.

Both numbers come from the same table. Where does the gap come from?

Month-endValue on hand
Mar 26$6.5M
Apr 26$5.9M
May 26$7.6M
Jun 26$6.8M
Jul 26$5.3M
Aug 26$6.9M
Card total (sum of all snapshots)$204.2M
Show the Answer and the DAX Hide the Answer and the DAX

Inventory is a snapshot, so it cannot be summed across time. The card adds up all 32 month-ends, which counts the same stock about 30 times over. The latest month-end alone is $6.9M.

The fix is to take the value at one date. Once the number is right, the story gets useful: $0.6M of it, 8%, has had no movement in six months or more.

Quick test for any card: filter to a single date and see whether the number drops by roughly the number of periods in the table. If it does, the measure is adding snapshots.

Month-endValue on hand
Mar 26$6.5M
Apr 26$5.9M
May 26$7.6M
Jun 26$6.8M
Jul 26$5.3M
Aug 26$6.9M
Card total (sum of all snapshots)$204.2M

The DAX

-- Wrong: adds every month-end together
Inventory Value (wrong) = SUM ( fact_inventory_snapshot[ext_cost] )

-- Right: value at the last snapshot in the period. The date is found with
-- every filter but the date cleared, so a part with no row on that date
-- reads blank instead of falling back to an older balance.
Last Snapshot Date =
CALCULATE (
    MAX ( fact_inventory_snapshot[snapshot_date] ),
    REMOVEFILTERS ( fact_inventory_snapshot ),
    VALUES ( dim_date[date] )
)

Inventory Value =
VAR LastSnap = [Last Snapshot Date]
RETURN
    CALCULATE (
        SUM ( fact_inventory_snapshot[ext_cost] ),
        fact_inventory_snapshot[snapshot_date] = LastSnap
    )

Slow Mover Value =
VAR LastSnap = [Last Snapshot Date]
RETURN
    CALCULATE (
        SUM ( fact_inventory_snapshot[ext_cost] ),
        fact_inventory_snapshot[snapshot_date] = LastSnap,
        DATEDIFF ( fact_inventory_snapshot[last_movement_date], LastSnap, DAY ) >= 180
    )

Issue 5 of 5

The Price Increase That Wasn't

Average selling price per unit, Q1 25 to Q3 26.

Average selling price per unit, company-wide, by quarter. It rises 11% from Q2 26 to Q3 26, to $81.00. Q3 26 holds 2 months of data so far (Jul, Aug). The sales lead wants to present it as the result of this year's price increase.

Before anyone takes credit for pricing, what would you check?

$60$70$80$90Q1 25Q2 25Q3 25Q4 25Q1 26Q2 26Q3 26Average selling price per unit, Q1 25: $76.93Average selling price per unit, Q2 25: $77.24Average selling price per unit, Q3 25: $76.77Average selling price per unit, Q4 25: $79.43Average selling price per unit, Q1 26: $78.75Average selling price per unit, Q2 26: $72.92Average selling price per unit, Q3 26: $81.00$76.93$81.00Average selling price per unit$60$70$80$90Q1 25Q3 25Q1 26Q3 26Average selling price per unit, Q1 25: $76.93Average selling price per unit, Q2 25: $77.24Average selling price per unit, Q3 25: $76.77Average selling price per unit, Q4 25: $79.43Average selling price per unit, Q1 26: $78.75Average selling price per unit, Q2 26: $72.92Average selling price per unit, Q3 26: $81.00$76.93$81.00Average selling price per unit
See the Numbers
PeriodAverage selling price per unit
Q1 25$76.93
Q2 25$77.24
Q3 25$76.77
Q4 25$79.43
Q1 26$78.75
Q2 26$72.92
Q3 26$81.00
Show the Answer and the DAX Hide the Answer and the DAX

Price the same parts at fixed 2024 prices and see whether the line moves anyway. The red line holds every part at its 2024 average price and changes only the mix of what was sold. It rises 11% over the same quarter, so almost all of the jump is customers buying more of the expensive parts.

An average price per unit across many parts is a mix measure as much as a price measure. A quarter that sells more of the high-priced parts looks like a price increase without a single price changing.

Habit worth building: any price KPI gets a fixed-mix companion, or better, a price-volume-mix bridge at the grain where prices are set.

$60$70$80$90Q1 25Q2 25Q3 25Q4 25Q1 26Q2 26Q3 26Average selling price per unit, Q1 25: $76.93Average selling price per unit, Q2 25: $77.24Average selling price per unit, Q3 25: $76.77Average selling price per unit, Q4 25: $79.43Average selling price per unit, Q1 26: $78.75Average selling price per unit, Q2 26: $72.92Average selling price per unit, Q3 26: $81.00$76.93$81.00Same parts at 2024 prices, Q1 25: $76.85Same parts at 2024 prices, Q2 25: $77.33Same parts at 2024 prices, Q3 25: $76.55Same parts at 2024 prices, Q4 25: $79.42Same parts at 2024 prices, Q1 26: $78.64Same parts at 2024 prices, Q2 26: $72.96Same parts at 2024 prices, Q3 26: $80.88$76.85$80.88Average selling price per unitSame parts at 2024 prices$60$70$80$90Q1 25Q3 25Q1 26Q3 26Average selling price per unit, Q1 25: $76.93Average selling price per unit, Q2 25: $77.24Average selling price per unit, Q3 25: $76.77Average selling price per unit, Q4 25: $79.43Average selling price per unit, Q1 26: $78.75Average selling price per unit, Q2 26: $72.92Average selling price per unit, Q3 26: $81.00$76.93$81.00Same parts at 2024 prices, Q1 25: $76.85Same parts at 2024 prices, Q2 25: $77.33Same parts at 2024 prices, Q3 25: $76.55Same parts at 2024 prices, Q4 25: $79.42Same parts at 2024 prices, Q1 26: $78.64Same parts at 2024 prices, Q2 26: $72.96Same parts at 2024 prices, Q3 26: $80.88$76.85$80.88Average selling price per unitSame parts at 2024 prices

The DAX

Revenue = SUMX ( fact_orders, fact_orders[qty] * fact_orders[unit_price] )
Units = SUM ( fact_orders[qty] )

Avg Price per Unit = DIVIDE ( [Revenue], [Units] )

-- The check: this period's units at each part's 2024 average price
Avg Price at 2024 Prices =
VAR BasePrices =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( dim_product[part_id] ), "@p", DIVIDE ( [Revenue], [Units] ) ),
        REMOVEFILTERS ( dim_date ),
        dim_date[year] = 2024
    )
-- Parts with no 2024 sales have no base price, so leave their units out of both sides
RETURN
    DIVIDE (
        SUMX ( BasePrices, [@p] * [Units] ),
        SUMX ( FILTER ( BasePrices, NOT ISBLANK ( [@p] ) ), [Units] )
    )

The definitions behind the measures are in the OEE, on-time delivery and TRIR entries. Issue 2 measures ship date because the demo table has no delivery date, so it is on-time shipping, not on-time delivery.