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()
)

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

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