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]
business intelligist 
Andre, i have registered both my work email and my fiancé email. We both received the email and clicked on the Active link, but then a window appear where is said “you are already registered” and then there were no connection to teams or any teams channel.
My email is martin (.) ***(@) ***(.)com if you are able to email me the link or fix it in some way.
the email with files is on its way, but also please try to access our Microsoft Teams channel for Knowledgebank.pro and see if you can get in
Hi, I love your PVM analysis series. specially the way you simplified the formulas of Price, Volume & Mix calculations. But, in one of your videos, you broke down the Mix variance further in to impact coming from new products & impact from discontinued products. I’m unable to find the formulas for New & Discontinued impact, is it possible for you to share with me those formulas. Thanks in advance.
yes I will be addressing this topic soon, stay tuned!
Usually I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man,Keep it up.
Andre, simply: THANK YOU VERY MUCH FOR THIS! Thank you very much for sharing your knowledge and for such a great serie of videos, simple and very well explained!!!
With you explanation, I was able to go ahead and transform the REVenue Analysis into MARgin Analysis as follow:
MAR TY – MAR LY = (REV TY – COGS TY) – (REV LY – COGS LY)
= (REV TY – REV LY) + (COGS LY – COGS TY)
= REV Impacts + (COGS LY – COGS TY)
= (Price Impact + Mix Impact + Volume Impact)(1) + (COGS LY – COGS TY)
(1) = From formulas above, which perfectly match at product level and in the total.
Thanks for all!!!!
Hi,
Further to the comment above, using the same rationale in the PVM formulas, if instead of using Price/AvgPrice values we would use the cost equivalents we would then be able to calculate a three way driven (cost/u,mix,volume) COGS variation right?
Usually, I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man, Keep it up.
Andre, i have registered my university email. I received the email and clicked on the Active link, but then a window appear where is said “you are already registered” and then there were not connection to teams or any teams channel.
My email is usm.edu.ec if you are able to email me the link or fix it in some way.
Cristina, I need your email address and then i can email it to you…
Hi How can we get the (Pbix) file to make sure our work is correct?
Usually, I never comment on blogs but your article is so convincing that I never stop myself from saying something about it. You’re doing a great job Man, Keep it up.
Hi,
I need help, how do I separate the Mix Channel and Mix Region inside de Mix Effect
Can you support how to get the example file ???
(Follow-Up to my first comment)
I think both formulas are wrong. If we consider yours for example, why would price impact be equal to V1 * (P1 – P0)? Why would the volume sold this year be used? Logically, we should consider the volume from last year: assuming the volume from last year remained the same, then R1 – R0 would be V1P1 – V0P0, i.e., as V1 = V0:
R1 – R0 = V0P1 – V0P0 = V0(P1-P0). This is the volume impact.
Similarly, assuming that the price remained the same (P1 = P0), then:
R1 – R0 = V1P1 – V0P0 = V1P0 – V0P0 = (V1-V0)P0. This is the price impact.
Now of course, in the general case, if you sum up both, they won’t add up:
V0(P1-P0) + (V1-V0)P0 = V0P1 – V0P0 + V1P0 – V0P0
= V0P1 + V1P0 – 2V0P0
which is not necessarily equal to V1P1 – V0P0 (R1-R0).
Thus there is some remainder effect missing. Let’s call it alpha. We are looking for alpha such that:
V0(P1-P0) + (V1-V0)P0 + alpha = V1P1 – V0P0
I let you do the math, but ultimately: alpha = (V1-V0)*(P1-P0)
Thus the revenue change from last year to this year is explained by 3 components:
Volume component = V0(P1-P0)
Price component = (V1-V0)P0
Volume-Price Interaction component (couldn’t find a better name for it) = (V1-V0)(P1-P0).
Hi Andre, thanks for sharing this. I’ve seen these formulas on other websites too, and I’m not sure they’re mathematically correct.
If I consider a company only sells 1 product (so mix effect is 0), assuming that P0, V0, P1 and V1 are respectively the price of the product last year, the volume sold last year, the price this year, and the volume sold this year, then, according to your formulas:
Volume Impact = V1 * (P1 – P0)
Price Impact = P0 * (V1 – V0)
Assuming revenue last year was R0 and this year it was R1 (=P1*V1), then you are saying that:
R1 – R0 = Volume Impact + Price Impact
= V1 * (P1 – P0) + P0 * (V1 – V0)
= V1P1 – V1P0 + V1P0 – V0P0
= V1P1 – V0P0
which makes sense of course. But we could also define things like this:
Volume Impact = V0 * (P1-P0)
Price Impact = P1 * (V1-V0)
Then
R1 – R0 = V0 * (P1 – P0) + P1 * (V1 – V0)
= V0P1 – V0P0 + V1P1 – V0P1
= V1P1 – V0P0
which is also true.
So how did you decide on which volume impact and price impact formula was the right one?
Hi Andre.
Thank your for all the posts and videos on PMV and waterfall charts. They have been incredibly helpful!
I was curious what is all available at knowledgebank.pro so I tried to register, but it requires admin approval. My IT team is concerned about giving access on “behalf of the entire organization” and that the site is not a certified Microsoft Enterprise application. Do you have any suggestions?
Again, I’m a huge fan of all your content and would love to see what else you offer in knowledgebank.pro
Thanks,
Al
Same for me. My org will not allow the connect to knowledgebank.pro. Is there a way to get the files directly?
Hi Andre, I also tried to register and supposedly my admin approved, but I don’t see anything. Can you send it directly, or advise how to get this
Thx for great videos! What would your reccomendation be w the tabular structure, I have raw data at its simplest form, product, date, sales amt, and units – I have 2 slicers a) for the base period and b) for the analysis period
Andre, i have registered my work email. I received the email and clicked on the Active link, but then a window appear where is said “you are already registered” and then there was not connection to teams or any teams channel.
Are you able to email me the link or fix it in some way.
Hi we will be moving everything to a new place this week and restarting regular posting, YouTube videos and also free courses… Bare with us for a few more days or a week or so please
Hi, we will be moving all of the labs to our website, https://www.obvience.com/courses-and-labs . It will probably take a few weeks but eventually everything will be available there.