fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
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.

Facebook
Twitter
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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

5 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.

  4. Maria says:

    Thank you. Very useful!

  5. Ibti says:

    Very useful! Thanks so much.

Leave a Reply