Edit: I recommend that you watch this video first as I cover the important theory in detail and then go to the Part II for the final calculation analysis.
Having been bombarded by many requests via LinkedIn, YouTube and this blog to provide additional information about Price Volume Mix calculation, I finally found some time to start working on a set of videos to offer more details about this subject. I have to say that I had to think very hard about how to present the theory of the calculation that is close enough to being perfect while keeping the formulas simple enough where we would still be able to implement this calculation fairly easily in Power BI without suffering significant performance implications on a data set with a large number of records in the Product dimension.
There are many complications in the PVM calculation that make it:
- difficult to understand
- difficult to implement in a tool like Power BI
- difficult to be performant on large datasets
So in this video I am going step by step with the main goal to make sure that you gain the understanding of what the calculation is striving to achieve and what drives the math behind each component of the calculation.
For the record, I am cutting corners a little bit for the sake of performance and I will discuss those compromises in the future videos; however, having analyzed a bunch of different ways to do it, I find the approach that I propose here to be the most practical one. If you have to get the calculation perfect, then after you have watched the video for Part I, go to the Part II, it’s much shorter and it provides the perfect math for the Mix component of PVM.