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()
)
business intelligist 
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!
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.
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
“Register” is not working. Please help. thanks
Could you check this process of registration ? It doesnt work for me 😦
Muchas gracias por compartirnos su trabajo. excelente aporte.
Quisiera tener acceso a los archivos si es posible. o quien me los puede compartir.
Hi, by any chance do you have this PBIX file? I am getting an error for calculating discontinued products for some reason. It is not showing any value.
Hi, thanks so much for this! Really helpful. Could you please help me with the logic if I want to run the deletions measure to be evaluated by month ie I have a product that is deleted in March of this year when looking at the full year result I want it to capture Mar- December as a deletion.
Great video. Quick question: what is the reasoning for the second term of the volume calculation, (Vol_TY_AT_LY_MIX – VOL_LY)*(P_LY-P_AVG), being included in the volume impact rather than mix impact? What does this term represent?
hello, invitation process doesn’t work
Hi Andre, I’ve played around trying to get the email invitation to get access to your docs, but have not been successful. Can you help by sharing the PBIX and excel file(s) you are using for this?
Thanks