Inspired by Challenge 48 in WorkoutWednesday, I wanted to blog about how this challenge opened up a new way of using reference bands in Tableau.
After learning how to create a waterfall chart in Tableau (see previous post here), the next challenge I had was finding a way to build this “profitability bridge”.
At first, I thought that the lines were made of a “line mark” in Tableau and then formatted to look like a dashed line. However, this was not possible. Next, I thought of using drop lines, however, I could not format it and cut it to only draw a line for the current sub-category as well as the next sub-category.
I finally realised that the way to format a line the way the target view had it, was through using a reference line!
When I first created a reference line, it looked something like this:
This was close, but it only covered one sub-category at a time. I needed a way to include the bottom half of the second sub-category.
To do this, we need a way for Tableau to recognise the bottom bit of the “next” sub-category. I knew that I could figure this out by using this logic:
The running sum of the sales – sum of sales
I then created this calculated field:
So now, we have a calculated field that we can use to refer to in the reference line to extend it to the “next” sub-category.
I then clicked “edit” on my current reference line. There was a problem – if I use a reference line, I can only refer to one calculation at a time – this means that I would not be able to extend my reference line to the next sub-category. I needed another function that would allow me to refer to 2 calculated field.
Enter –> Reference Bands
I set my reference band to first refer to the first calculation which the normal SUM(Sales) and the second one to refer to my newly calculated field: “Sales for Ref Line”. This was the result:
The line had successfully extended to include the “next” sub-category.
Set your labels to “none” and set your fill option to “none”.
And there you go – a new creative way to build a bridge using reference bands.