fbpx

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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

Demo of Power BI Variance chart

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.

Variance chart in Power BI - Just actual values

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.

After adding variance figures in another visual

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:

conditional formatting rules - variance chart in power bi

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?

how to synchronize chart sorting between visuals in Power BI

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:

Use inner padding 50% to make the variance bars thinner
  • 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.

Grouping visuals in Power BI

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

two level data validation drop down excel

Two-level Data Validation [Excel Trick]

Ever wanted to create a two-level data validation list? You can use this simple trick to make two-level or cascading drop-down validation lists in Excel. You need some data, a pivot table and simple IF formulas to get this. Read on to understand the process and create your own two-level drop down lists in Excel.

3 Responses to “How to make a variance chart in Power BI? [Easy & Clean]”

  1. Ajay says:

    Short & complete ?? would like to see many more on power bi visualization

  2. Zoli says:

    Super-clear ?

  3. radhaaariv says:

    Such a informational and useful blog post about how to make a variance chart in Power BI. Thanks for that post it has plenty of information to consider. Keep sharing such amazing blog posts with us.

Leave a Reply