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

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

  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.

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

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

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

  9. 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?

Leave a Reply to steven jones Cancel 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 )

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