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]



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

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