Price Volume Mix Analysis Using Power BI (2024)

EDIT 2020-12-14: the final installment of the three part series on PVM with DAX formulas and the latest video can be found here: https://businessintelligist.com/2020/12/11/dax-for-perfect-price-volume-mix-pvm-calculation-power-bi-tutorial/

EDIT 2020-11-20: the most recent post about this topic, including a video, can be found here:https://businessintelligist.com/2020/11/20/the-definitive-guide-to-price-volume-mix-calculation-pvm-in-power-bi-theory/

EDIT: 202-04-24: I have made some additions to the original post, please see the theory behind the calculations in the link below:
https://businessintelligist.com/2020/04/24/price-volume-mix-pvm-for-revenue-variance-analysis

Also, if you would like to know how to calculate PVM for Gross Margin analysis, you can follow this link: https://businessintelligist.com/2020/04/26/price-volume-mix-pvm-for-gross-margin-variance-analysis

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)

[EDIT 2020-12-14: there is also a different approach to the PVM calculation that is very popular in FP&A analysis that can be found here (including a video) : https://businessintelligist.com/2020/12/11/dax-for-perfect-price-volume-mix-pvm-calculation-power-bi-tutorial/

This approach uses different logic for calculations:

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)

Please follow the link above if you would like to learn how that approach works as well. END OF EDIT]

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:

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)

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:

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

SalesCY= SUM(Sales[Revenue])
SalesPY= CALCULATE(Sales[SalesCY],SAMEPERIODLASTYEAR(‘Date'[Date]))
SalesYoY= Sales[SalesCY]–Sales[SalesPY]
VolumeCY= SUM(Sales[Volume])
VolumePY= CALCULATE([VolumeCY],SAMEPERIODLASTYEAR(‘Date'[Date]))
PriceCY= DIVIDE(Sales[SalesCY],[VolumeCY])
PricePY= DIVIDE(Sales[SalesPY],[VolumePY])

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

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:

PriceVolumeMix=

VARPriceImpact=
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Price”,([VolumePY])
*(
IF(
ISBLANK([PricePY])||ISBLANK([PriceCY]),
0,
[PriceCY]–[PricePY]
)
)
),
[Price]
)

VARVolumeImpact=
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Volume”,(
IF(
ISBLANK([PricePY])||ISBLANK([PriceCY]),
0,
[VolumeCY]–[VolumePY]
)
)
*([PricePY])
),
[Volume]
)

VARMixImpact=
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“Mix”,IF(
ISBLANK([PricePY])||ISBLANK([PriceCY]),
0,
([PriceCY]–[PricePY])
)
*([VolumeCY]–[VolumePY])
),
[Mix]
)

VARNewProductImpact=
SUMX(
ADDCOLUMNS(
VALUES(‘Product'[Product]),
“New”,IF(ISBLANK([SalesPY])||[SalesPY]=0,[SalesCY],0)
),
[New]
)

VARDiscontinuedProductImpact=Sales[SalesYoY]–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.