# DAX for Perfect Price Volume Mix (PVM) Calculation (Power BI Tutorial)

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]

```

## 20 thoughts on “DAX for Perfect Price Volume Mix (PVM) Calculation (Power BI Tutorial)”

1. Martin says:

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.

1. Business Intelligist says:

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

2. Rapto says:

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.

1. Business Intelligist says:

yes I will be addressing this topic soon, stay tuned!

3. Russel David says:

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.

4. Val says:

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!!!!

1. Lucas says:

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?

5. Russel David says:

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.

6. Cristina says:

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.

1. Business Intelligist says:

7. Mohammad A M Mubarah Mubarah says:

Hi How can we get the (Pbix) file to make sure our work is correct?

8. steven jones says:

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.

9. Alessandra Lins says:

Hi,

I need help, how do I separate the Mix Channel and Mix Region inside de Mix Effect

10. Mehdi HASSOUNI says:

(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).

11. Mehdi HASSOUNI says:

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?