Price Volume Mix Analysis Using Power BI

9 comments

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

1/1/2015

D1

1000

10

2/1/2015

D1

1000

10

1/1/2015

P1

3500

10

1/1/2016

P1

4000

17

1/1/2016

N1

1000

10

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:

PVM

Order

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])
SalesPY = CALCULATE(Sales[Sales CY], SAMEPERIODLASTYEAR(‘Date'[Date]))
SalesYoY = Sales[Sales CY] – Sales[Sales PY]
VolumeCY = SUM(Sales[Volume])
VolumePY = CALCULATE([Volume CY], SAMEPERIODLASTYEAR(‘Date'[Date]))
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 =
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Price”, ([Volume PY])
* (
IF(
ISBLANK([Price PY]) || ISBLANK([Price CY]),
0,
[Price CY] – [Price PY]
)
)
),
[Price]
)


VAR VolumeImpact =
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Volume”, (
IF(
ISBLANK([Price PY]) || ISBLANK([Price CY]),
0,
[Volume CY] – [Volume PY]
)
)
* ([Price PY])
),
[Volume]
)


VAR MixImpact =
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Mix”IF(
ISBLANK([Price PY]) || ISBLANK([Price CY]),
0,
([Price CY] – [Price PY])
)
* ([Volume CY] – [Volume PY])
),
[Mix]
)


VAR NewProductImpact =
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“New”IF(ISBLANK([Sales PY]) || [Sales PY] = 0, [Sales CY], 0)
),
[New]
)


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


RETURN
SWITCH(
MAX(PVM[Order]),
1, PriceImpact,
2, VolumeImpact,
3, MixImpact,
4, NewProductImpact,
5, DiscontinuedProductImpact,
BLANK()
)

You can download the sample here.

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

  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?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s