Price Volume Mix Analysis Using Power BI

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

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

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