Price Volume Mix (PVM) Calculation in Power BI with New and Discontinued Products (Power BI Dax Tutorial)

Price Volume Mix (PVM) calculation is one of the most popular topics on my blog and YouTube channel. Although, I have covered the DAX for the perfect PVM before, I have gotten a lot of requests to extend the PVM logic to include a separate bucket for discontinued products as well as new products. You can watch the video below to get all the details on how to add those two buckets to your Waterfall chart.

DAX used in the tutorial:


Impact (Price) = 

VAR PVMProduct = 
FILTER(
    'Product', 
    [Units LY]*[Units TY]*[Revenue LY]*[Revenue TY]+0<>0
)

RETURN
SUMX(
    PVMProduct, 
    [Units TY]*[Price Diff TY to LY]
)

Impact (Volume) = 

VAR PVMProduct = 
FILTER(
    'Product', 
    [Units LY]*[Units TY]*[Revenue LY]*[Revenue TY]+0<>0
)

RETURN
SUMX(
    PVMProduct,
    var VOL_TY = [Units TY]
    var VOL_LY = [Units LY]
    var Vol_TY_AT_LY_MIX = [Units TY @ LY Mix]
    var P_AVG = [Avg Price LY]
    var P_LY = [Price LY]

    return  (VOL_TY - VOL_LY)*P_AVG + 
            (Vol_TY_AT_LY_MIX - VOL_LY)*(P_LY-P_AVG)
)
Impact (Mix) = 

VAR PVMProduct = 
FILTER(
    'Product', 
    [Units LY]*[Units TY]*[Revenue LY]*[Revenue TY]+0<>0
)

return
SUMX(
    PVMProduct,
    [Units TY Mix Gain]*[Price Diff to Avg LY]
)
Discontinued Products = 

VAR DiscontinuedProducts = 
FILTER(
    'Product', 
    [Units TY]*[Revenue TY]+0=0
)

return
-1*SUMX(
    DiscontinuedProducts,
    [Revenue LY]
)
New Products = 

VAR NewProducts = 
FILTER(
    'Product', 
    [Units LY]*[Revenue LY]+0=0
)

return
SUMX(
    NewProducts,
    [Revenue TY]
)
PVM = 
SWITCH(
    SELECTEDVALUE('PVM Walk'[SortOrder]),
    1, [Impact (Price)],
    2, [Impact (Volume)],
    3, [Impact (Mix)],
    4, [Discontinued Products],
    5, [New Products],
    BLANK()
)

5 thoughts on “Price Volume Mix (PVM) Calculation in Power BI with New and Discontinued Products (Power BI Dax Tutorial)

  1. Again, a great video and explained in a very understandable manner, thanks so much!
    Were you able to sort out (wordplay ;)) the sorting issue in the waterfall chart, having both a start and ending value using the switch formula approach, you posted last year? The chart won’t let me sort by the breakdown, and even though I have custom sorting on my breakdown categories, PBI ignores it and continues to sort by total value of the breakdown :/

    Thanks a lot and keep up the great work!

  2. Andre, this is out of control; you’re a superstar for helping educate all of us on the power in Power BI and Power Platform. Thank You!

    Really loving the Forecasting lab too.

    Would you be so kind to see if there is hold-up with access to Teams? Apart from the first email after registration, I receive “You are already registered”.

    No pressure, as I am most grateful for these resources.

  3. I’m in the same situation “You are already registered”, if possible I would appreciate the access for the resource pack.

    Thank you in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s