Part I – The Definitive Guide to Price Volume Mix Calculation (PVM) in Power BI – Theory

EDIT 2020-12-14: if you are looking for the perfect mathematical formula and the DAX necessary to implement PVM, please go the latest post/video in this series – DAX for Perfect Price Volume Mix (PVM) Calculation (Power BI Tutorial)

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:

  1. difficult to understand
  2. difficult to implement in a tool like Power BI
  3. 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.

12 thoughts on “Part I – The Definitive Guide to Price Volume Mix Calculation (PVM) in Power BI – Theory

  1. A very nice presentation indeed. I do think that your students might find the downloadable spreadsheet to be an easier introduction into these complexities. The calculation of mix is the important issue here and is thoroughly explained in The math is the same whether one is analyzing Gross Margin or, in your case, Sales. The site also includes information on the “real world” problem of consolidation with downloadable spreadsheets and a “Deep Dive” .pdf.

  2. 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.

  3. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s