In this tutorial, I will show you how to create a sales variance dashboard in Power BI, which decomposes sales variance in quantity, price and mix variance. In particular, the variance dashboard highlights the main changes between budget and actual and allows you to drill-down on them: capitalize on positive variances and address negative variances.

Below is what the final dashboard looks like: a summary of the variances, then a focus on quantity, price and mix variance.

The components of sales variance: quantity variance, price variance, and mix variance

Sales variance

Sales variance is the total change in sales between budget and actual. In the example below, a sales increase of 1% is explained by a strong and positive price variance of 2%, a flat mix variance and a -1% negative quantity variance. 

Next, I will explain each variance in detail. If you are looking for a more in-depth explanation of the variances, please check the resources at www.double-entry-bookkeeping.com. They have a great explanation of sales variance here and the difference between quantity and mix variance here.

Quantity variance

The first reason for a change in sales is a change in the number of units sold; this is known as 'quantity variance'. The calculation is simple, and it's equal to the percentage increase (or decrease) in the units. 

In addition, this quantity variance excludes the impact of the change in mix, which is separately analyzed.

In the example below, 122 fewer units are sold in actual compared to budget. This corresponds to a quantity variance of -1%.

Price Variance

The second reason for a change in sales is a change in price: this is called the 'price variance'. 

The price variance depends on 2 factors: how much the prices increase and on which product the prices increase. Prices increasing on a product with higher sales, for example, have a bigger impact than prices increasing on a product with lower sales.

In the example below, the price increase in Ford has an impact of 2% on the total company sales. At the same time, the price drops of Suzuki and Acura are compensated by the price increases of Cadillac and Audi.

Mix Variance

The final reason for a change in sales is the change in mix, called the 'mix variance'.

The mix impact of a product is positive when the product is growing faster than the average sales, and negative when the product is growing slower than the averages sales. In the example, total units are dropping at -1% so products growing at a rate faster than -1% have a positive mix variance. 

The size of the variance (be it positive or negative) depends then on the product price: the higher the price, the bigger the variance.

In our example, GMC and Acura are growing faster than the average and create a positive effect for 5%. This positive impact is completely voided by the drop in Ford, Land Rover, and Honda. The total mix effect is therefore 0%.

The dashboard also shows the mix variance with the detail of:
– products added (not in budget): 5% such as GMC
– products removed from budget (not in actual): -5% such as Land Rover and Honda
– other products which are in both budget and actual

Creating the sales variance dashboard in Power BI

Load Budget and Actual numbers in Power BI

You can load numbers in Power BI from the 'Get data' icon in the Home tab.

In addition, you can download the sample data for Budget from here and for Actual from here to follow this tutorial.

Create a Power BI calculated table to calculate sales variance

After the data is loaded, we create a table that will do all the necessary variance calculations. You can go to 'Modeling', 'New Table', paste the below code and then click on Commit.

Code for creating the sales variance table

Here is the code for calculating the variances. The explanation is below.

Code explanation

The first part of the code creates a table with all products.

Part 2 of the code adds units and sales to the products table. Following that, part 3 adds product prices, 'mix type' and the 'units if no change in mix'. 

The 'mix type' is:
– 'added' if the product is in actual but not in budget, 
– 'removed' if the product is in budget but not in actual
– 'other' for products that are both in actual and budget

The 'units if no change in mix' corresponds to the units in actual if the product mix did not change. As an example, for a product that accounted for 20% of units sold in budget, actual units are recalculated to also account for 20% of total units.

Once all the above information is in the table, part 4 calculates the quantity, price and mix variances. If this calculation is unclear, please refer to an explanation of the price variance here, and quantity and mix variance here.

Finally, step 5 unpivots the tables. Unpivoting is needed for the data to be in the right format for the visualizations. If you want to learn about unpivoting in DAX, you can check the post here.

One last measure

Once the table is ready, you can add a 'percentage variance' measure to be used in the visualization, as below:

That's it!

Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.