Power BI How-To / Finance Analysis

Price Volume Mix Analysis in Power BI: Explain Revenue Change with DAX

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

Four decisions that make the bridge add up

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.

Two comparable periods

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.

Separate new and lost products

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.

A waterfall, not a table

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.

What does a price volume mix analysis explain?

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.

What are the formulas for price, volume and mix effects?

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:

  1. Volume effect = ( total Q1 − total Q0 ) × prior average price, where prior average price = total prior revenue ÷ total Q0.
  2. Mix effect = sum of ( Q1 × P0 ) per product − total Q1 × prior average price.
  3. Price effect = sum of ( P1 − P0 ) × Q1 per product.

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.

What does a worked price volume mix example look like?

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.

Illustrative sample data, not client results: units, average price and revenue per product in each period (prices in generic currency units). Product C is new this period; Product D sold only last period.
Product Prior units Prior price Prior revenue Current units Current price Current revenue
Product A100101,0008011880
Product B100202,000140192,660
Product C (new)–––3015450
Product D (lost)2025500–––
Total220–3,500250–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.

How do you handle new and lost products?

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:

The complete revenue bridge for the sample data above, from prior-period revenue to current-period revenue.
Bridge step Amount What drove it
Prior revenue3,500Products A, B and D last period
Volume+30020 more like-for-like units at the prior average price of 15
Mix+300Shift from Product A toward the higher-priced Product B
Price−60Product B’s cut outweighed Product A’s increase
New products+450Product C, first sold this period
Lost products−500Product D, no sales this period
Current revenue3,990Products 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.

Which DAX measures calculate each effect?

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] ) ).

The five effect measures. Volume, Mix and Price use the LFL and AvgPricePY variables shown above; the measure references inside SUMX evaluate once per product.
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.

How do you show the bridge as a Power BI waterfall chart?

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.

Why don’t the volume and mix effects add up by product?

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:

  1. Does the bridge fail to close? Compare the five effects with [Revenue] - [Revenue PY]; a gap usually means returns or credit notes carry revenue with zero units.
  2. Are units in the same unit of measure in both periods? A switch from cases to eaches looks like a huge volume gain and price cut.
  3. Is a slicer on a product attribute removing items from one period only? Slice on the Product dimension, never on a column in the Sales table.
  4. Are rebates or freight booked outside the invoice line? They will not appear in realized price unless they are allocated to products.
  5. Is the current period still open? A partial month compared with a full prior month shows up as negative volume.

When should a senior engineer build it for you?

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.

FAQ

Frequently asked questions

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.

Try us risk-free for 4 hours

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.

Want a revenue bridge your finance team can trust?

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

Book a free demo

See what your Power BI reporting would take

Tell us the task. We reply within one business day, and you get a scope estimate and the hours needed before any work begins.

Step 1 of 2: What do you need?

How can we help? Pick one

Tap to add, or type your own:

Step 2 of 2: Where should we reply?

Used only to contact you about this inquiry.

We reply within one business day. Scope approved before any hours are used. We don’t sell your information.

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.

  • Prepaid hours from $100/hour — and they never expire
  • Senior-led delivery — 20+ years on the Microsoft platform
  • Typical 3–5 business day turnaround
  • Full refund if you’re unhappy in your first 4 hours
Book my free demo