One yardstick: prior prices
Volume and mix are valued at last period’s prices, and price is measured on this period’s units. Fix that convention once and the three effects always sum to the actual revenue change.
Power BI How-To / Finance Analysis
A Power BI price volume mix analysis splits the change in revenue between two periods into three causes: selling more or fewer units, selling a different blend of products, and charging different prices. This guide gives you the formulas, a worked example, DAX measures that keep new and lost products in their own buckets, and a waterfall chart that shows the bridge from one period to the next. Finance teams that bridge margin instead of revenue often call it a rate volume mix analysis: the price effect becomes a rate effect, and the same formulas apply.
The short version
Volume and mix are valued at last period’s prices, and price is measured on this period’s units. Fix that convention once and the three effects always sum to the actual revenue change.
Current and prior units and revenue come from the same measures shifted by a marked Date table. Year over year, quarter over quarter and actual versus budget all reuse the same effect logic.
A product with no prior sales has no prior price, so it cannot have a price effect. New and discontinued products get their own bridge steps instead of distorting mix.
The built-in waterfall chart turns the effects into a bridge readers grasp at a glance: each step floats up or down, and the closing column lands on the net change.
A price volume mix analysis explains why revenue moved between two periods. It splits the total change into a volume effect from selling more or fewer units, a mix effect from selling a different blend of products, and a price effect from charging more or less per unit. The three effects add back to the actual change.
A single revenue variance hides offsetting stories. Revenue can be flat while a price increase is quietly cancelled by customers shifting to cheaper products, or it can grow only because a high-priced line sold more while everything else shrank. Splitting the variance tells sales, pricing and finance teams which lever actually moved. It is the natural next question after a budget vs actual report in Power BI: once you know revenue missed or beat the plan, price volume mix tells you why.
Use prior-period prices as the yardstick. Volume effect is the change in total units times the prior average price. Mix effect is current units at each product’s prior price, minus current units at the prior average price. Price effect is each product’s price change times its current units. Together they equal the revenue change.
Written out, with Q for units, P for average realized price (revenue divided by units), 0 for the prior period and 1 for the current one:
Add them and the terms cancel to current revenue minus prior revenue, which is why the bridge always closes. Using realized price rather than list price matters: discounts, rebates on the invoice line and promotional pricing all show up in the price effect, which is usually what a commercial team wants to see. If you plan to run the bridge on margin instead of revenue, set up the base measures from our guide to gross margin analysis in Power BI first, so margin % totals are recomputed rather than averaged.
Take two continuing products. Product A drops from 100 to 80 units while its price rises from 10 to 11; Product B climbs from 100 to 140 units while its price slips from 20 to 19. Revenue rises 540, which splits into 300 of volume, 300 of mix and minus 60 of price.
| Product | Prior units | Prior price | Prior revenue | Current units | Current price | Current revenue |
|---|---|---|---|---|---|---|
| Product A | 100 | 10 | 1,000 | 80 | 11 | 880 |
| Product B | 100 | 20 | 2,000 | 140 | 19 | 2,660 |
| Product C (new) | – | – | – | 30 | 15 | 450 |
| Product D (lost) | 20 | 25 | 500 | – | – | – |
| Total | 220 | – | 3,500 | 250 | – | 3,990 |
Work the like-for-like pair first. Units rose from 200 to 220 and the prior average price was 3,000 ÷ 200 = 15, so volume is 20 × 15 = 300. At prior prices the current units are worth 80 × 10 + 140 × 20 = 3,600, against 220 × 15 = 3,300 at the average price, so mix is 300: customers moved toward the more expensive product. Price is 80 × (11 − 10) + 140 × (19 − 20) = 80 − 140 = −60. The sum, 540, is exactly 3,540 − 3,000. Notice what a plain variance would have hidden: Product B’s price cut cost more than Product A’s price rise earned.
Keep new and lost products out of the price, volume and mix math. A new product has no prior price, and a lost product has no current price, so neither has a price change to measure. Report each as its own bridge step: new-product revenue as an increase, lost-product prior revenue as a decrease.
Forcing them into the formulas breaks the split. Treat Product C as having a prior price of zero and its whole 450 lands in the price effect; leave it in the unit totals and it inflates volume at someone else’s price. With separate buckets the full bridge reads cleanly:
| Bridge step | Amount | What drove it |
|---|---|---|
| Prior revenue | 3,500 | Products A, B and D last period |
| Volume | +300 | 20 more like-for-like units at the prior average price of 15 |
| Mix | +300 | Shift from Product A toward the higher-priced Product B |
| Price | −60 | Product B’s cut outweighed Product A’s increase |
| New products | +450 | Product C, first sold this period |
| Lost products | −500 | Product D, no sales this period |
| Current revenue | 3,990 | Products A, B and C this period |
Decide up front what counts as “new”. A product that sold one unit last year technically continues, and its tiny prior volume can produce a huge mix or price swing. Some teams set a minimum prior volume before a product joins the like-for-like set; whichever rule you choose, write it on the report page.
You need four base measures (units and revenue for the current and prior period) and five effect measures. The price, volume and mix measures iterate the product list with SUMX, restricted to like-for-like products that sold in both periods; the new and lost measures pick up the rest, so the five effects sum to the revenue change.
The base measures assume a Sales table with Quantity and Amount columns, a Product dimension and a marked Date table: Units = SUM( Sales[Quantity] ), Revenue = SUM( Sales[Amount] ), Units PY = CALCULATE( [Units], SAMEPERIODLASTYEAR( 'Date'[Date] ) ) and Revenue PY = CALCULATE( [Revenue], SAMEPERIODLASTYEAR( 'Date'[Date] ) ). If the date shifting is new to you, our guide to time intelligence DAX and Date tables covers the setup. Each effect measure starts from the same like-for-like set, VAR LFL = FILTER( VALUES( 'Product'[Product] ), [Units] > 0 && [Units PY] > 0 ), and the prior average price, VAR AvgPricePY = DIVIDE( SUMX( LFL, [Revenue PY] ), SUMX( LFL, [Units PY] ) ).
| Measure | RETURN expression | What it returns |
|---|---|---|
| Volume Effect | ( SUMX( LFL, [Units] ) - SUMX( LFL, [Units PY] ) ) * AvgPricePY |
The unit change valued at the prior average price |
| Mix Effect | SUMX( LFL, [Units] * DIVIDE( [Revenue PY], [Units PY] ) ) - SUMX( LFL, [Units] ) * AvgPricePY |
Current units at each product’s prior price, less the same units at the average |
| Price Effect | SUMX( LFL, [Revenue] - [Units] * DIVIDE( [Revenue PY], [Units PY] ) ) |
Current revenue less current units at prior prices, which equals the price change times current units |
| New Products | SUMX( FILTER( VALUES( 'Product'[Product] ), [Units] > 0 && NOT ( [Units PY] > 0 ) ), [Revenue] ) |
Revenue from products with no prior-period units |
| Lost Products | - SUMX( FILTER( VALUES( 'Product'[Product] ), [Units PY] > 0 && NOT ( [Units] > 0 ) ), [Revenue PY] ) |
Prior revenue from products with no current units, as a negative |
Two details make these work. A measure reference inside an iterator triggers context transition, so [Units PY] inside SUMX is calculated for one product at a time; Microsoft’s SUMX function reference (opens in new tab) documents the iterator behavior. And NOT ( [Units PY] > 0 ) is true for a blank, which is what a product with no prior sales returns. To check the model, add a measure for [Revenue] - [Revenue PY] and confirm it equals the five effects added together on every slice.
Load the steps into a small disconnected table, write one switch measure that returns the right effect for each step, and put both on the built-in waterfall chart. Category is the step, the Y-axis is the switch measure, and the chart closes with a Total column that equals the net change.
Create the step table with PVM Step = DATATABLE( "Step", STRING, "Sort", INTEGER, { { "Volume", 1 }, { "Mix", 2 }, { "Price", 3 }, { "New products", 4 }, { "Lost products", 5 } } ), set Step to sort by the Sort column, and leave the table unrelated to the model. The value measure is PVM Value = SUMX( VALUES( 'PVM Step'[Step] ), SWITCH( 'PVM Step'[Step], "Volume", [Volume Effect], "Mix", [Mix Effect], "Price", [Price Effect], "New products", [New Products], "Lost products", [Lost Products] ) ); wrapping the SWITCH in SUMX means the total is the sum of the steps rather than a blank. On the visual, sort the axis by Step ascending so the bars stay in bridge order. If readers want to see the starting point, add a “Prior revenue” row with Sort 0 that returns [Revenue PY]; the Total column then lands on current revenue instead of the net change. Microsoft’s guide to waterfall charts in Power BI (opens in new tab) covers the Category and Breakdown wells and the sort options.
Putting Product in the Breakdown well splits each bar into its largest contributors, which works well for the Price step. Use it with care on Volume and Mix, for the reason in the next section.
Because mix only exists across a group. On a single product row, there is no blend to change, so the mix effect is zero and the whole unit change shows as volume. At the total, the same units split into volume and mix. Price is additive; volume and mix are not, by design.
In the sample data, Product A on its own shows volume of (80 − 100) × 10 = −200 and Product B shows (140 − 100) × 20 = +800. Those rows add to 600, which is exactly the total’s volume of 300 plus mix of 300. The matrix is not wrong; it is answering a different question at each level. If you need product-level contributions that add to the total, allocate the mix effect with the per-product form, ( Q1 − total Q1 × prior unit share ) × ( P0 − prior average price ), which gives +150 for each product here. Beyond that, most broken bridges come from a short list of causes:
[Revenue] - [Revenue PY]; a gap usually means returns or credit notes carry revenue with zero units.Bring in help when the bridge goes in front of a board or a pricing decision and the data is messy: several currencies, unit-of-measure changes, rebates booked outside the invoice line, or product codes that were renumbered. The DAX here is short; agreeing the definitions and cleaning the product key is most of the work.
Power BI Flex Support is prepaid, senior-led Power BI hours that never expire, with no contracts and no project minimums. Blocks are Starter $2,500, Growth $4,600 and Power $8,000, and hourly work starts from $100. You send the request, we reply with a scope and an hours estimate before any work, hours are deducted only for work you approve, and most tasks land 3–5 business days after approval. Distributors with large SKU catalogs often want the bridge alongside fill rate and margin, which is part of what we build in wholesale distribution dashboards. If the bridge is only the visible symptom of a tangled model, the same hours cover data modeling and DAX work. Contact is through the form on this site.
Related reading
FAQ
This guide values volume and mix at prior prices and price at current units, a widely used convention. Other orders are valid and give slightly different splits, because the interaction between price and quantity lands in whichever effect is calculated last. Pick one convention, document it on the report and keep it fixed.
Either works with the same measures. For year over year, the prior measures use SAMEPERIODLASTYEAR. For budget, point the prior measures at budget units and budget revenue instead, which needs a budget table planned at product level. A budget held only at revenue level cannot be split into price, volume and mix.
Yes. Swap revenue for gross margin and price for margin per unit, and the same measures split the margin change into volume, mix and a rate effect. The rate effect blends price and unit cost movements, so split it further if you need to see them separately. Keep the revenue bridge alongside for context.
Measure mix at the level where the business makes choices. Iterating products shows product mix; iterating categories folds product shifts within a category into the price effect. You can also run the same pattern over customers or regions. Changing the iterated column changes the split, so label which level the report uses.
Yes. A senior, US-based, Microsoft-certified engineer can build the measures and the waterfall on your own sales data from prepaid Flex hours that never expire. Blocks are Starter $2,500, Growth $4,600 and Power $8,000, with hourly work from $100. We quote the hours before starting, and most tasks land in 3 to 5 business days after approval.
If you’re unhappy within the first 4 hours of your block, we’ll refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, and they never expire.
Bring your sales data to a free demo. We agree the price, volume and mix conventions with you, build the measures and the waterfall, and check that the bridge closes, on prepaid hours that never expire. Most tasks land in 3–5 business days.
Scope approved before any hours are used · Hours never expire
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.