EDIT 2021-03-18: I have added two additional buckets for PVM analysis with New Products and Discontinued Products in this new blog post/video.
This is the third installment in the series; however, you are not required to read the previous two to follow along.
Part I – I frame up the basic concept of analysis in and suggest a light way easy formula for the PVM calculation
Part II – I refine the Mix component of PVM to make it perfect at the expense of adding a little bit of complexity to the logic.
and now in Part III we get perfect math for all of our formulas.
These are the formulas that I used in the video
PRICE IMPACT = Volume TY * (Price TY – Price LY)
MIX IMPACT = Units TY Mix Gain * (Price LY – AVG Price LY)
VOLUME IMPACT = (Vol TY – Vol LY) * Avg Price LY +
(Price LY – AVG Price LY) * (VoL @ LY Mix – Vol LY)
I have made a small adjustment to the Volume Impact to make sure that the logic nets perfectly both for every line item and total overall.
Since the files used in this tutorial are still work in progress, I will not be posting them here quite yet, but if you cannot wait, then you can register at http://knowledgebank.pro , accept the email invitation and then use Microsoft Teams to switch to KnowledgeBank.Pro tenant and grab the files from there.
All the details are in the video below:
These are some of the DAX measures that I have implemented in the tutorial
Impact (Price) = SUMX( VALUES('Product'[Product]), [Units TY]*[Price Diff TY to LY] ) Impact (Mix) = SUMX( 'Product', [Units TY Mix Gain]*[Price Diff to Avg LY] ) Impact (Volume) = SUMX( 'Product', 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) ) Total Units LY = CALCULATE( [Units LY], ALL('Product') ) Total Units TY = CALCULATE( [Units TY], ALL('Product') ) Avg Price LY = CALCULATE( [Price LY], ALL('Product'[Product]) ) Mix LY = DIVIDE( [Units LY], [Total Units LY] ) Mix TY = DIVIDE( [Units TY], [Total Units TY] ) Price Diff to Avg LY = [Price LY] - [Avg Price LY] Units TY @ LY Mix = [Total Units TY]*[Mix LY] Units TY Mix Gain = [Units TY] - [Units TY @ LY Mix]