PlantOps BI

Price-Volume-Mix

Revenue on the demo orders grew +$7.87M from 2024 to 2025. A price-volume-mix bridge splits that into selling more, selling a richer mix, and charging more. Measured part by part, almost none of it is price.

Revenue bridge at part grain, 2024 to 2025, from fact_orders by order date.
  1. 2024 revenue $72.19M
  2. Volume +$6.48M
  3. Mix +$1.34M
  4. Price +$52K
  5. 2025 revenue $80.06M

The Grain Decides What Counts as Price

With the same items in both years, volume is the same at any grain: the change in units, priced at last year's average. (A part that is new this year sits in the new and lost bucket at part grain, but inside an existing family it counts as volume at family grain.) Mix and price are not. The coarser the grain, the more of the mix shift lands in "price", because a family or company average price rises when customers buy more of the expensive parts, even if no part's price moved.

The same 2024 to 2025 change, decomposed at three grains.

GrainVolumeMixPrice
Part (200)+$6.48M+$1.34M+$52K
Product family (5)+$6.48M+$1.16M+$226K
Company total+$6.48M$0+$1.39M

A commercial team reading the company-grain bridge would credit pricing with +$1.39M. Part by part, pricing moved +$52K; the rest is customers buying a different mix. Decompose at the grain where a price is actually set, which in a plant is usually the part or the part and customer.

The DAX

The measures iterate parts sold in both periods, price each at its own average last year, and compare. Use them on a card or in a table by family; each row recomputes over the parts inside it, so the family rows show part-grain effects, not family-grain ones. Price adds up across rows, because it is a sum over parts. Volume and mix do not: a shift from one family to another is mix at the total but volume inside each family row, so the family rows' volume and mix won't sum to the total's.

Revenue = SUMX ( fact_orders, fact_orders[qty] * fact_orders[unit_price] )
Units = SUM ( fact_orders[qty] )
Revenue PY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( dim_date[date] ) )
Units PY = CALCULATE ( [Units], SAMEPERIODLASTYEAR ( dim_date[date] ) )

-- Like-for-like parts: sold in both periods. Each part is priced at its own
-- average selling price last year, which is what keeps mix out of price.
Revenue at PY Prices =
SUMX (
    FILTER ( VALUES ( dim_product[part_id] ), [Units] > 0 && [Units PY] > 0 ),
    [Units] * DIVIDE ( [Revenue PY], [Units PY] )
)

Volume Effect =
VAR Parts = FILTER ( VALUES ( dim_product[part_id] ), [Units] > 0 && [Units PY] > 0 )
VAR Q0 = SUMX ( Parts, [Units PY] )
VAR Q1 = SUMX ( Parts, [Units] )
RETURN ( Q1 - Q0 ) * DIVIDE ( SUMX ( Parts, [Revenue PY] ), Q0 )

Mix Effect =
VAR Parts = FILTER ( VALUES ( dim_product[part_id] ), [Units] > 0 && [Units PY] > 0 )
VAR Q0 = SUMX ( Parts, [Units PY] )
VAR Q1 = SUMX ( Parts, [Units] )
RETURN [Revenue at PY Prices] - Q1 * DIVIDE ( SUMX ( Parts, [Revenue PY] ), Q0 )

Price Effect =
VAR Parts = FILTER ( VALUES ( dim_product[part_id] ), [Units] > 0 && [Units PY] > 0 )
RETURN SUMX ( Parts, [Revenue] ) - [Revenue at PY Prices]

-- Whatever is left: parts sold in only one of the two periods
New and Lost Parts =
[Revenue] - [Revenue PY] - [Volume Effect] - [Mix Effect] - [Price Effect]

SAMEPERIODLASTYEAR needs a date table marked as one, with no gaps. If the date table runs past your last day of data, a filter on the current year compares a part year with the whole of last year; end the date table at the last loaded date, or filter to completed months. Every part on the demo data sold in both years, so the new and lost bucket is zero here. The site's tests check that the bridge adds up exactly at every grain, that volume is the same at every grain, and that mix plus price is constant while the split between them moves.

4-wall margin · About the demo dataset