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
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?
See the Numbers
| Period | Boise plant OEE |
|---|---|
| Jan 25 | 60.1% |
| Feb 25 | 60.5% |
| Mar 25 | 63.5% |
| Apr 25 | 60.9% |
| May 25 | 64.2% |
| Jun 25 | 63.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.
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: 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?
See the Numbers
| Period | Value | Note |
|---|---|---|
| Mar 26 | 94.8% | 100% shipped |
| Apr 26 | 94.5% | 100% shipped |
| May 26 | 94.8% | 100% shipped |
| Jun 26 | 95.0% | 100% shipped |
| Jul 26 | 94.8% | 100% shipped |
| Aug 26 | 97.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.
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
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?
See the Numbers
| Period | Value |
|---|---|
| Feb 25 | 0.0 |
| Mar 25 | 0.0 |
| Apr 25 | 0.0 |
| May 25 | 0.0 |
| Jun 25 | 8.4 |
| Jul 25 | 0.0 |
| Aug 25 | 0.0 |
| Sep 25 | 0.0 |
| Oct 25 | 0.0 |
| Nov 25 | 0.0 |
| Dec 25 | 0.0 |
| Jan 26 | 15.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.
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
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-end | Value 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-end | Value 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, 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?
See the Numbers
| Period | Average 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.
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] )
)