Simple variance report with a forecast

Follow along this guide

If you want to follow along this guide in your Power BI, download this zip archive and open the file 2 Zebra BI segmented charts - forecast drilldown.pbix:

Download the follow-along PBIX file

Creating a simple variance chart and selecting the right type

You need three measures: your actual sales to date (September in this case), your sales plan for the year and your sales forecast for the remainder of the year.

Insert a new Zebra BI PowerCharts custom visual and simply drag the following measures into the corresponding fields in your visualization properties:

  1. Drag actual sales to date to the Values field
  2. Drag the sales plan to the Plan field to tell the visual that these are planned values

This gives you a chart with actual values and variances. Each bar represents an actual value (dark grey) and the variance (red or green bar) compared to plan.

Use a column chart if you have a small number of categories, such as 6 to 12 months. When working with a larger number of data points, like a weekly time series or multiple years, you should use an area chart or a line chart with highlighted differences.

Tip: Click on variance values to show relative or absolute variance or both at the same time.

Even though you can display absolute and relative variance on the column chart, you should use available space to expand the visual. Since Zebra BI visuals are fully responsive, absolute and relative variances will then be shown separately to improve readability.

Use the arrows < and > at the left and right sides of the visuals to change the chart to another advanced business chart.

A great chart to use in this case is an area or a line chart. Bar charts tend to use a lot of ink as the bars grow over the year. The best practice is to use minimal designs like the below area chart or a line chart. This way, your eye is drawn to the actual variances and not to the overall chart size.

Tip: In the area chart, you can even remove the grey area by setting opacity to zero to just view the variances.

Adding a forecast

Adding forecasts is easy - you just drop the forecast values from your model to the Forecasts segment and Zebra BI will do the rest.

What you get is a simple bar chart with forecasts.

Forecast values are shown differently to distinguish from other values.

Again, you can change the visualization type by clicking on the arrows < and > at the side of the visuals.

A waterfall chart is a good choice for reviewing this type of data - it shows totals, the budget and forecasts with variances. Waterfall chart is particularly well suited for this purpose, because it shows how individual values contribute to the end result.

Tip: You can cut off the axis in a waterfall chart by simply clicking on one of the total columns. However, you should note that this might make monthly contributions seem larger than they really are.