Price Volume Mix Analysis Using Power BI

I have made some additions to the original post, please see the theory behind the calculations in the link below:

Also, if you would like to know how to calculate PVM for Gross Margin analysis, you can follow this link:

I have also recorded a video about the subject here:

The original post continues below:

Traditionally, when we do variance analysis, we compare the actual value of the metric being measured to its target value in either absolute or relative terms. For example, if I were to compare current year sales (Sales CY) to prior year sales (Sales PY) I might want to create a Sales YoY calculation as well as a Sales YoY % calculation. A vast majority of companies do not go much further than that, as they use these two variables as well as the original two (Sales CY and Sales PY) to either trend or scatter plot them using either Product, Division or Time lenses in chart’s Axes or Series.

Price Volume Mix variance analysis adds a little bit more sophistication to the aforementioned approach as it enhances our initial analyses by decomposing how volume or pricing changes of our product assortment contributed to the difference in performance between the actual and target values.

Traditionally, Price Volume Mix analysis has the following three components:

  • Price Impact = Target Volume * (Actual Price – Target Price)
  • Volume Impact = Target Price * (Actual Volume – Target Volume)
  • Mix Impact = (Actual Volume – Target Volume) * (Actual Price – Target Price)

I will add some additional insight to our calculation and introduce two more components to the mix:

  • New Products Impact = Actual Sales of products that were not present in the Target scenario
  • Discontinued Products Impact = Target Sales of products that were present in the Target scenario but we did not get any actual sales for the time period being analyzed

A cursory search of the interwebs regarding the latter two lenses into the variance impact did not yield any content; therefore, I feel that I can claim both thought-leadership for myself 🙂 and also tool leadership for Power BI as it enables such a powerful path of variance examination.

Even though this is a level 300 article, the data set that we will use here is extremely simple:

Date Product Revenue Volume





















As you can see, we have sales history of three products D1, P1 and N1 (in this naming convention D stands for Discontinued and N stands for New just so it’s easier to test and follow the logic). In other words, we only sold D1 in 2015 and we only sold N1 in 2016 where as we Sold P1 in both 2015 and 2016. So, in our example we will use year 2016 as Actual and year 2015 as Target.

Because this is a level 300 lab, I will not be providing step by step instructions; I will, however, provide all of the DAX logic that is necessary to implement this solution (the sample file is available to download at the end of the article)

Create Date and Product tables

Frankly speaking, the only table that we really need in addition to the Sales table is Date because it is required to implement time intelligence logic. I did create a Product table as well simply to illustrate how one might use DAX to do it.

Product Table: Product = VALUES(Sales[Product])

Date Table: Date = CALENDAR(“1/1/2015”“12/31/2016”), then add a Year field: Year = YEAR(‘Date'[Date])

I also created an “Enter Data” table by pasting in the following:



Price 1
Volume 2
Mix 3
New Products 4
Discontinued Products 5

After that I linked my Sales table with Date and Product.

Basic measures

Sales CY = SUM(Sales[Revenue])
SalesYoY = Sales[Sales CY] – Sales[Sales PY]
VolumeCY = SUM(Sales[Volume])
PriceCY = DIVIDE(Sales[Sales CY], [Volume CY])
PricePY = DIVIDE(Sales[Sales PY], [Volume PY])

These measures should be self-explanatory for a 300-level lab.

Price Volume Mix variance calculation

The basic flow of this calculation is the following:

  1. We create local variables that calculate Pricing, Volume, Mix, New Products and Discontinued Products impact
    1. For Pricing, Volume and Mix calculations, we need to make sure that we are only considering products that have sales this year and prior year. To do that we use AddColumns() function to get a list of all products and then add relevant data to each product record to calculate necessary impact. Checking for Blank value for price current year or prior year insures that in these calculations we only analyze products that had both current and prior year sales
    2. New products logic is very similar other than we check that no prior year sales exist
    3. Discontinued Products logic is basically a cheat – we solve for it by subtracting the other variables from the Sales YoY variance.
  2. We use Switch() function to check for the selected member in the PVM table that would be adding to the Category of the Waterfall Chart and then we return the corresponding local variable

The final Price Volume Mix calculation is listed below:

Price Volume Mix =

VAR PriceImpact =
“Price”, ([Volume PY])
* (
ISBLANK([Price PY]) || ISBLANK([Price CY]),
[Price CY] – [Price PY]

VAR VolumeImpact =
“Volume”, (
ISBLANK([Price PY]) || ISBLANK([Price CY]),
[Volume CY] – [Volume PY]
* ([Price PY])

VAR MixImpact =
ISBLANK([Price PY]) || ISBLANK([Price CY]),
([Price CY] – [Price PY])
* ([Volume CY] – [Volume PY])

VAR NewProductImpact =
“New”IF(ISBLANK([Sales PY]) || [Sales PY] = 0, [Sales CY], 0)

VAR DiscontinuedProductImpact = Sales[Sales YoY] – VolumeImpact  – NewProductImpact – PriceImpact – MixImpact

1, PriceImpact,
2, VolumeImpact,
3, MixImpact,
4, NewProductImpact,
5, DiscontinuedProductImpact,

You can download the sample here.

33 thoughts on “Price Volume Mix Analysis Using Power BI

  1. This is very helpful. I wanted to download this sample file, but nothing happens when I click on it. It the link working properly?

      1. It’s working now! Thanks.
        On Wed, Apr 5, 2017 at 9:45 AM, business intelligist wrote:
        > Business Intelligist commented: “the link works for me, can you try it > again?” >

    1. I could be misunderstanding your question/comment, but by definition, when we do variance analysis we trying to determine how well we did relative to our expected or target value, so we multiply target volume on difference in price and then target price in difference in volume.

      1. in the vpcm calc, determining price or cost variance is the only one that is straightforward.. (actual price – target price) * Actual volume… you don’t multiply by the target volume. Also, the way the formula is built for volume is directional correct but not 100% accurate… it does include portion of the mix.. The mix formula mentioned above needs to be revised too

      2. Hello,
        Deepak is right, by definition we use target price when we caclulate volume impact, and actual volume when price impact.
        You can find the details of calcuations whith all explanations with the links below :

        But at the same time somebody uses your methode :

        Any ideas why people use different methods ?

        Thank you!

      3. I think the question is what two variables we are trying to bridge. This year number vs last year number, forecast vs actual, etc. So if I compare this year to last year, then I would hold one of the drivers from last year (like price) and analyze the change in the other driver year over year (like volume) etc.

  2. This is exactly what I was looking for. Great article and thank you for providing the downloadable example. How would you perform this with multiple levels in a hierarchy where all the calcs are performed at the Product level and then sum up to a Product Line level and then Product Lines sum to create Categories. This would be done in Tableau using a LOD function.
    Thanks again.

  3. Hi, really like the implementation of this in Power BI. One question, generally I consider mix to be a shift of volume to either a more expensive or cheaper product. Consider this example:

    Product Date Revenue Volume
    P1 1/01/2015 20 20
    P1 1/01/2016 40 40
    P1 1/01/2017 60 60
    P2 1/01/2015 36 180
    P2 1/01/2016 32 160
    P2 1/01/2017 28 140

    The price for P1 stays constant at $1 and the price for P2 stays constant at $0.20. The volume increases for P1 while the volume for P2 decreases however their combined volume remains constant at 200 units. This shift in volume results in an increase in revenue. When I put this data into your example only the volume showed a change while the mix remained at 0. Can you think of a way to represent this as a change in mix rather than a change in volume?

  4. Hi,
    This is extremely helpful. Thank you so much for posting this. Do you know if I can create a similar analysis framework to analyze gross margin variance? In other words, gross margin change due to price change, raw material cost change, shipping cost change, new product, discontinued product and product mix. Any guidance you can provide is highly appreciated.

  5. I like your approach, but as Jason mentioned in his post above, the mix and volume variances’ calculations don’t make sense.
    It probably should be:
    Mix Variance = (old product / old volume all products) * (new volume product / old volume all products) * (New Vol – Old Vol)*Old Price
    Volume variance = (New Vol – Old Vol) * Old Price – Mix Variance

  6. i think the correct PVM formula should be as follows

    Price Var (Pcy-Ppy)*Vpy
    Vol Var (Vcy-Vpy)*Pcy – Mix
    MixVar (Vcy – Vpy)*Pcy*[(Vcy/VcyTOT)-(Vpy/VpyTOT)]

    Where, cy – current period, and py – prior period, TOT – sum total for the period

  7. Hi. This is great. I have learned from it and downloaded and applied it on my own work. I was wondering if its possible to have this linked. What I mean is if I click on one of the variances in the waterfall, that then it filters on my other tables to show me only the specific customer (i replaced your product view by customer) on my tables which have all the data.

  8. This is great!, Thanks for the post and for uploading the doc! I replicated this and it has worked!. However, I have put the chart and a table below showing the products and numeric data of interest to complement the analysis and when I wanted to filter something from the chart it doesn’t worked as expected (for instance, I want to click on the “New products” bar and get the table filtered showing only those products with the details. I reviewed the Interactions and they are ok. What am I doing wrong?

  9. This is very interesting and something I have been struggling to get up and running! I need a consultant to help with implementing PVM in Power BI in my business. Do you do any consultancy/freelance work?

  10. Great info! I’m however having trouble to get the yearly information correct when a product is diversified mid year and there has been a price change. The months calculates correctly but the yearly value is not a sum of the months as it is calculated just the same as the months. Is there a way to handle that?

    1. In lack of response, I created some SQL that calculates the volume/price components correctly. Still using the Powerpivot for the analysis (where the full year is a sum of the months and not a calculation).

  11. Super interesting…will it be possible to throw in a level of extra complexity adding a geography dimension with multiple countries. The ADDCOLUMNS plays tricks on my when working with two separate dimension from two tables?

  12. Very interesting,

    How can I showcase the following:

    Column A: Should be the Full Year of April 2018, i.e.: base line
    … and then the bridge in between as you show in this article.
    Column B: Sum of all the previous resulting in FY April 2019 Sales.

  13. I’ve been using a pretty similar model as well. But sometime i found it hard to explain to the model users the mix portion of the equation. Any ideas how to further decompose the mix component into market segment mix, channel mix and regional mix ? I assume it would change the volume variance as well?

  14. Hi, thanks for sharing the file. I’ve successfully made the similar chart following your article.
    I’m wondering how can I adopt this chart to a waterfall chart that has PY and CY bars? So, the most left bar is PY, the middle bars are Price, Volume, Mix, New Products, Discontinued Products, and the most right bar is CY.

  15. Hi, thanks for sharing the file. I’ve successfully made the similar chart following your article.
    I’m wondering how can I adopt this chart to a waterfall chart that has PY and CY bars? So, the most left bar is PY, the middle bars are Price, Volume, Mix, New Products, Discontinued Products, and the most right bar is CY.

  16. i highly appreciate your useful article. It makes my day. Btw, just one step further, please help me how can I filter by product (N1,P1,D1) as your model? please help me .

  17. Your Volume and Mix analysis here is way too complicated . . . and, actually, it is incorrect. The correct answer for Volume and Mix analysis is mathematically (with “proofs”) very simple. We don’t have to be forced to worship a script calculation simply because it is done in BI. In terms of basic concepts, an Excel spreadsheet is more than sufficient. I would like to see you explain this analysis to top level management . . . lots of luck on that one! If you are really interested in the correct solution for Volume and Mix analysis, take a click on

  18. Hi

    First of all, let me clarify that I am using power query and power pviot for sales analysis and have imported sales register in the data model and created a calendar. Now I want to calculate the price and volume variance calculation so that I can run the sales analyses using various cuts.

    About the data:
    I have a sales data which has detailed transaction level details wherein the details are available date wise, invoice-wise, product-wise. I want to do the PV analyses at product code level and then aggregate it at various levels (product category, market segment, customer segment etc).

    Please help me writing a DAX measure for price volume analyses. I tried writing DAX measure to calculate the “sum of product-wise sales value” and “sum of product-wise sales quantity”. Similarly I can write previous year formula if I know this. Further, using these numbers I can calculate average price for current year and Previous year and then can calculate Price variance at product level. Volume variance I can put as balancing number (i.e. ([Product-wise sales value of current year] – [Product-wise sales value of previous year]) – Price variance at Product level)

    Appreciate if someone can help with this as I struggled to find solution for last couple of days.

Leave a Reply

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

You are commenting using your 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