September 8th
February 22nd
Price Volume Mix Variance Analysis in Power BI
When it comes to your revenue variance, you want to have a complete insight into the factors which drive the changes you are seeing. You usually want to look for the most profitable products in your portfolio, the effects your product mix has on your revenue and the state of your margins. The best way to get this done is with a Price Volume Mix analysis.
Many businesses struggle with it, particularly when using Power BI. That’s why we’ve created a comprehensive guide to the PVM analysis which will help you add another dimension to your business reporting and thus improve your understanding of your business.
Read on to learn all about creating it by using Zebra BI visuals in Power BI. If you'd like to jump straight to the template for Power BI instead, grab it here.
Overview
This article explores how you can improve your business dashboards by including a Price Volume Mix analysis. It will help you learn to create rich Power BI reports that tell a compelling story.
We will start by explaining the three core concepts which make up this analysis and show you how to prepare your data for it in Power BI. Next, we will go on to show how to use Zebra BI for Power BI to visualize this analysis. Finally, we will discuss two different approaches to it and show which of the two delivers better results.
Why a typical business dashboard is not enough
Business dashboards usually deal with revenue, gross profits, income and possibly some other KPIs, and compare the current results to the ones from the previous year, plan, or another target. Working from there, you can start analyzing variances by business units, geographical areas or other measures.
While this is a good start, you might need more insight. This is where the Price Volume Mix analysis comes in. In its most basic version, it shows you how individual factors, such as price changes, sales volumes and product mix affect your revenue.
Let’s take a look at each individual component:
- Price – Price shows the price at which you sell your products. It also works in conjunction with the margins. A price increase directly translates into improved margins. Keep in mind, however, that higher prices may result in lower volumes, as fewer customers decide to buy higher-priced products.
- Volume – This is the second factor you should look at. It reflects the number of products you sell. The volumes you sell affect your revenue – the more products you sell, the higher your revenue. However, the volume has little effect on your profit margins. Selling more products at lower prices even reduces your profitability if the cost of goods remains unchanged.
- Mix – Not all products are created equal. Let’s say you sell two versions of a product. The premium version costs more and has a better profit margin than the regular one. This means that the changes in your product mix will affect your revenue, as selling more products with better profit margins drives up the revenue and vice versa.
Granted, the Mix category is a bit vague, but it provides very important and compelling insights. It essentially explains how your product mix affects the revenue. For example, you can look at whether the products you’re selling this year are of a higher value compared to last year’s. If the answer is yes, the mix will turn out to be positive Increased sales of less valuable products, on the other hand, might drag your mix down, resulting in lower margins.
The Price Volume Mix analysis is an important way of looking at your business
Let's take a look at the price. The price is one of the key factors affecting your growth and performance as a company. The relation is pretty straightforward – price increases usually mean improved performance. Volume is another factor driving your company's growth. A growth in volume normally correlates to better performance, unless it is offset by something else.
Take a look at how Zebra BI shows you this data:
Let's review what the visualization shows.
It shows that a 5.5% growth in sales can be attributed to different reasons. It then allows you to take a look at what drove the change. For example, increases or decreases in product prices over the past year might have increased the volume or decreased it. What about individual products? Are you selling more items of the same product than last year or fewer? You can also take a look at the total effect of the volume increases?
Once you get into the Price Volume Mix variance analysis, you can get really creative. Instead of just focusing on previous year’s data, you can analyze the changes in budget. Instead of using revenue, you can use your contribution margins or your gross profit, which will make the story even more powerful. Using the profit, in particular, makes this analysis 10 times or 20 times more insightful.
Adding more dimensions
There is also no reason to just use these three categories. After all, your business has more drivers. Two typical dimensions you could take a look at are the new and discontinued products. The new products will grow your business and you can do an analysis to identify the revenue that was achieved with just them. Another thing to take a look at is the impact of the discontinued products. These products translate into less revenue in a specific segment. Adding all this data to your analysis can give you a much clearer picture of what impacts your sales.
This is a great example of how powerful this type of analysis can be. At a glance, you can see whether new products are offsetting the revenue lost from discontinued products. You can see the movements in your prices and product volume while also keeping your finger on the pulse of the performance of your product mix.
It is clear that the Price Volume Mix variance analysis should become an essential tool in your reporting belt. You can use it for ad-hoc analyses or make it a regular part of your quarterly or annual reports. Let’s take a look at how to prepare and organize your data and do these calculations in Power BI.
Price Volume Mix analysis in Power BI
The first thing you need to do is prepare data. In this example, we are using the table imported from Excel, but you can import data from other sources supported by Power BI. These include SQL databases, plain text files, web sources and many others.
An important thing to do here is to separate your products from this table, especially if you have a sales table with all columns on a single table. An easy way to do this in Power Query is to create a reference to your sales table, rename it and remove all columns but the ones containing products and product groups to create the dimension of products.
When you return to your sales model, you will have your sales table along with a separate product table. Then you need to make sure that the link relationship is one to many. This is how it should look in the end.
Setting up Price, Volume and Mix variance measures
Once we have our separate products table, we have something to work with. In the Fields pane, you have Quantity and Revenue for the current and previous year. Now you can start calculating your Price field, meaning your average revenue per unit. That is very easy to do. Just divide revenue by quantity and add "zero" at the end. Or - even better - use the blank function here for handling the division by zero.
Price AC = DIVIDE([Revenue AC], [Quantity AC], BLANK())The next step is again calculating the Price change field, which is just the difference between the actual and the previous year's price.
The Price variance gets a bit trickier. Let's take a look at the formula and then go through it step by step.
∆Price = SUMX(Products, IF([Price PY] * [Price AC] <> 0,[Price change] * [Quantity AC],0))This formula takes the price change for every product and multiplies it by the actual quantity. The IF statement is used to check for active products only and exclude any new or discontinued products. Then we use the SUMX to aggregate everything from the product level to all of the hierarchies above that. That is why SUMX is used here with the products table and why it is so important to have a completely separate table for products.
After this, the Volume variance is very straightforward.
∆Volume = IF([Quantity PY] * [Quantity AC] <> 0, ([Quantity AC] - [Quantity PY]) * [Price PY])For the volume variance, calculate the change in the quantity and multiply it with the price for the previous year.
To calculate the Mix variance, you can use the following formula.
∆Mix = ([∆PY]) - ([∆Volume] + [∆Price] + [New] + [Discontinued])After calculating the total variance by subtracting the previous year's revenue from this year's revenue, you simply subtract everything. Subtract the volume change, price change, and new and discontinued products. This provides you with your Mix variance.
Creating the Price Volume Mix analysis visualization
Now is a good time to use Zebra BI visuals for Power BI to visualize your data. However, before you start that, here is a simple trick to make it all easier. As you work with your data, you end up with many separate measures. However, for charts and tables and so on, you can make it simpler by collecting the measures in a single category.
You do this by creating a separate disconnected table which I named PVM. This simple table is not connected to any data and is just a list of values.
The table contains short and long names of measures and a SWITCH statement is used to transfer the value from those measures into this table.
Revenue by PVM = SWITCH(MAX(PVM[ID]),
1, [Revenue PY],
2, [∆Price],
3, [∆Volume],
4, [∆Mix],
5, [New],
6, [Discontinued],
7, [Revenue AC])In this case, the values are transferred from the actual measures. This is just the way to unpivot all the measures and put them into one dimension for easier handling.
This allows you to take a Zebra BI chart and simply take the Category measure from the PVM table and drop it onto the Category field in your Visualization pane.
This puts all categories on the axis. The only way for this visualization to work, you need to use a waterfall chart. This is very easy to do – simply add a Zebra BI Charts visual, select Revenue by PVM measure, and add the Category measure to the Category field. All that's left is right-clicking on Revenue AC and Revenue PY column headings to mark these values as Results.
If you are using subtotals, you can easily just mark any additional columns as subtotals. This will help you in your analysis.
You could also easily create Small multiples by dropping the Product group measure into the group field.
This delivers a great-looking view with small multiples by product groups.
The key to creating a price volume mix analysis in Power BI is to start by preparing your data. Once your data is ready, advanced visualization tools in Zebra BI for Power BI make it really easy to create analyses that drive engagement and tell a story.
Get a free template and easily create a next-level Price Volume Mix Analysis in Power BI. Download it now and save yourself a bunch of work.
Related Resources
Price Volume Mix Variance Analysis in Excel
Top 9 Power BI Dashboard Examples
Excel Report Templates: Build Better Reports Faster
