How to Build a Variance Bar Chart

How do you display this month's Sales have not surpassed last month's Sales?

In a past blog post here I talk about using a Reference line to display last month's Sales values.

VizDJ Steve Adams created a short video clip here showing you how you can take a bar chart with reference lines to the next level by adding Variance calculations!

Print screen from Steve's video

I asked Steve if he could share those calculations with me and he did! His workbook also covered a Graphomate extension.

I took his dashboard and gave it a techtipsgirl spin here in Tableau Public.

How to Create this Chart

First start with your Month over Month (or Year over Year) Date Calculations

Latest Date

{Fixed : Max([Month Year])}

*Month_Year is my date column

Latest Month

Date(DATETRUNC('month',[Latest Date]))

Previous Month

Date(DATEAdd('month',-1,[Latest Date]))

Then you need to create your calculations for the values or measures for these date values.

Sales Current Month Current Year

IF DATEPART ('year',[Order Date]) = DATEPART('year',[Latest Year])

AND DATEPART ('month',[Order Date]) = DATEPART('month',[Latest Month])

then [Sales]

END

*Sales Previous Month Current Year

IF DATEPART ('year',[Order Date]) = DATEPART('year',[Latest Year])

AND DATEPART ('month',[Order Date]) = DATEPART('month',[Previous Month])

then [Sales]

END

*If you want this to be up to the current date, then you will need to add a line for the day value "AND DATEPART ('day',[Order Date]) <= DATEPART('day',[Latest Date])"

Steve created this chart by using the following three calculations

Sales MOM Base

IF SUM([Sales Curr Month CY])>SUm([Sales Prev Month CY])

THEN SUm([Sales Prev Month CY]) Else SUM([Sales Curr Month CY])

END

Sales MOM VAR -ve

IF SUM([Sales Curr Month CY])<SUm([Sales Prev Month CY])

THEN SUm([Sales Prev Month CY]) - SUM([Sales Curr Month CY])

END

This calculation will display the Sales gap between this month's Sales and the prior month's Sales reference line.

Sales MOM VAR +ve

IF SUM([Sales Curr Month CY])>SUm([Sales Prev Month CY])

THEN SUM([Sales Curr Month CY])- SUm([Sales Prev Month CY])

END

This calculation covers the positive Sales that was made over the reference line to the prior month's Sales.

I placed Sub-Category on the Rows shelf

Then I placed Measure Values on the Columns shelf.

I removed everything but the Sales MOM VAR -ve, Sales MOM VAR +ve and Sales MOM Base from the Measure Values Marks card. (Make sure the base calculation is at the bottom of the marks card

Then I added the Sales Current Month Current Year calculated field to the Rows shelf after Sub-Category and changed it to discrete so it will display before the bars in the chart.

I placed Sales Previous Month Current Year to the Detail Marks card.

I moved Measure Names to Color and I added a border around the bars.

I changed the base color to White. I used the Color-Blind friendly RAG colors from this past blog article for the other two calculated fields.

I performed house keeping and removed gridlines, formatted my Sales calculations as Currency etc.

Then I needed to add the Reference line to the bar chart. I did this based off of the Sales Previous Month Current Year calculated field. I also made my reference lines darker and wider than the default.

I also formatted the label as shown below to ensure the Labels were BEFORE the Reference line.

And that's it! Feel free to ping Steve Adams and tell him how much you like this chart.

We are partnering on an upcoming Intermediate to Advanced Course soon! Email him at stevea@vizdj.com if you are interested! You will learn intermediate to advanced concepts in a 3 day 2 hour each day course. It's designed as a hands on bootcamp so that you will immediate be able to take what you learned and start using it.

There is a fee for the course because we will be using Steve's software.

I am doing the course for fun as I love teaching. I recently changed roles and my previous manager made me promise that I would keep teaching.

You will learn how to make this dashboard;

Here are the concepts covered;

Day 1

Wireframing, KPI building, MoM analysis, Reusable calcs, URL Actions, Download options, Navigation design

Day 2

Metric swapping, Dual Axis, Standard deviation bands, Intermediate calcs, Top 5, Enhancing bar charts, Parameter actions

Day 3

Measure names / Measure values, Set actions, Sheet swapping, Padding, Navigation buttons, Custom layouts, LOD Expressions