How to make a variance chart in Power BI? [Easy & Clean]

Power BI is great for visualizing and interacting with your data. In this article, let me share a technique for creating variance chart in Power BI. Variance charts are perfect for visualizing performance by comparing Plan vs. Actual or Budget vs. Actual data.

This is what we are going to build.

What you need to build a variance visual in Power BI?

You need a few basic measures.

• Actual values
• Variance
• Variance %

These measure definition will depend on what you are visualizing. So I won’t go in to that detail, but 99% of time, they will simple aggregations such as SUM or AVERAGE.

Power BI Variance charts – Video

If you want a quick walk thru of the variance chart construction, check out below video. Read on for text instructions.

Variance chart in Power BI – Step by step instructions

Step 1: Make a bar / column chart with Actual values

This is easy. Just make a bar (or column) visual with actual values. It will look like this.

Step 2: Add a similar chart for variance and place it next to the actual chart

I would just copy and paste the visual and change the measure. We will end up with this.

Step 3: Apply conditional formatting rules to color variance bars

Select variance visual, go to Format > Data colors and click on fx button to apply conditional formatting rules. You want green color for positive values and red for negative values (assuming positive variance is good, else flip your rules).

The rule setup looks like this:

At this stage, our variance chart looks like this. It has a teeny tiny problem. The sort order is not in sync between both visuals. ?

Step 4: Synchronize sort order trick

You can sort both visuals in alphabetical order, but that would make the charts less useful. So let’s stick with the descending order of actual values.

Just add “Actual Sales” measure to the tooltip area of variance bar chart. Now you can sort that chart on “Actual Sales” too. Pretty neat eh?

Step 5: Tidy up the formatting & group visuals

This is the last step. Just clean up the formatting of both charts. These are the steps:

• For actual values bar chart:
  • Color the bars in something dull
  • Remove Y & X axis titles
  • Remove visual title
• Variance bar chart:
  • Adjust Y axis “inner padding” . This makes the bars thin. See right.
  • Remove Y axis – title & all
  • Knock off X axis titles and visual title
• Make sure both visuals are in same height and top-aligned. Adjust the width of variance bar chart if not done.

Now just select both visuals (hold CTRL key to multi-select) and group them. This way you can move or resize them together.

That is all. Your variance chart is now ready. Enjoy it.

You can enhance it by adding a table that shows detail, something like this:

Download Variance Chart sample workbook

If you are not sure about the instructions or just want a ready to use example, here is the Power BI workbook. This file also features a tool-tip that shows performance at sub category level.

New to Power BI? Check out this excellent getting started guide.

Want something like this in Excel? See this budget vs. actual chart.

Note: Thanks to recent webinar Andrej & Reza for the inspiration to this article.