Part II - The Definitive Guide to Price Volume Mix (PVM) Calculation in Power BI - The Perfect Mix
ฝัง
- เผยแพร่เมื่อ 23 ก.ย. 2024
- In the Part I of this series, I mentioned that I was cutting some corners with my calculation for the sake of giving you a simple and performant formula that was “good enough”. Well, what if it is not good enough and you will not settle for anything other than the perfect math???
The good news is that it took me half the time of the Part I Video to cover the perfect formula in the video for the Part II. It takes a few more steps and it will take a bit of performance out of your reports, but this guide would not really be The Definitive one if I did not give you the perfect calculation, would it???
Link to blog post: businessintell...
This series of videos is amazing. Looking forward to the PowerBI model.
Coming soon!
Nice video! One thing I am a bit puzzled about is that the revised mix formula intuitively makes more sense, but creates an off balance when you sum the price, product, mix impacts and compare it with the YoY variance. It ties from a consolidated view, but when you add up the individual components at a product level, it does not. Why is that?
This was very helpful, thanks for sharing !!
Glad it was helpful!
looking forward for a power bi implementation of this
should be posted some time this week, stay tuned
@@KnowledgeBankPro I'm interested too!
@@KnowledgeBankPro Any update on when this will be ready?
@@Game_Impala in the next day or two... the model and the excel files are ready, just need to find some time to record the video
@@arazorbackguy11 almost there...
Thank you so much for this. I have applied these formulas to my P&L and the components do in fact add up to the revenue deltas perfectly. Something puzzles me though. I am applying this to a P&L that basically only has number of units sold and avg selling price of ALL units (total revenue / total units). So I don't see the mix impact on a product level. I do see the mix impact in the end results, but I'm not sure I understand. If my calculation says a +10K variance due to mix, is it implied that within all of the products we sold, we sold more "good" products than "bad", even though I don't know which products were actually sold?
i have all dax and revised math for PVM covered here, hope it helps: th-cam.com/video/te5kKFqrB8U/w-d-xo.html
Nice work Andre
Is the assumption here that the margins are the same for product a & b? Theoretically you could sell more of lower priced products but have better margin
good question, this approach is for revenue analysis only, GM analysis is a little bit more involved - I will need to cover that in depth later
All you have to do is replace revenue with GM$'s.
You can use this calculation to find the impact of change between two timeframes. Doesn't have to be YoY. It could be month over month or day over day.
hi
what is the formula for PVM MIX?
For every product, you need to calculate what the units would have been at last year mix for this year total. Then you deduct that number from this year units. This is the number of units that affect the mix. Then you multiply that number by product price last year minus average price last year and it will give you mix. Then you need to sum them all up for all products