Custom Row Banding
Recently, my good friend, Ghafar Shah, sent me a message about a problem he was having with a dashboard at work. He was trying to create custom row banding in Tableau. Essentially, he had rows of charts on a sheet (like in the header image), but didn't want the standard row banding on every other line (like Tableau does). He wanted to customize the row banding - essentially choose (or let his users choose) which rows are banded. In his case, he was already using the dual-axis, which left us with fewer options.
We did get a solution to this problem, and although it might be a bit niche, I thought it was an interesting problem that had an interesting solution. So why not share it?
If you'd like to follow along in Tableau, please download this workbook from Tableau Public. Just unhide all sheets. The sheets are numbered in order of the steps we will take in this blog post. And you can ignore the one called "Hide Me".
Okay, here's the chart we are starting with. It's just a row for each of 8 subcategories charting sales over time. It uses a dual-axis where one axis is an area chart and the other is a line (my favorite way to design an area chart).
So, we could use Tableau's built-in row banding options. To do this, we right-click on the chart, choose Format, go to the paint bucket, and then to Row Banding:
From here, we can change both the Pane and Header color to what we like, then we can move around the levers below them. I'll be honest, I always have to fiddle with them to see what each lever does. But if we set it as shown in this screenshot, you'll get row banding on every other row:
It's nice to have a few options, but this is not what Ghafar's stakeholder needed. The stakeholder needed to have custom row banding - essentially, they wanted to dictate what rows were banded. So lets do that!
To make row banding a bit more dynamic, I created a set on Sub-Category called Sub-Category Set. (In my public workbook, you'll see that set along with my calculations in the Row Banding folder on the left). However, you could hardcode the rows that you want to band in a calculation if you choose.
Okay, next, let's create a simple calculation called Custom Row Banding:
IF [Sub-Category Set] THEN 0
ELSE NULL
END
This will yield a 0 for anything that is in the set and a null for anything not in the set. And yeah, I know, it's not necessary to write the "ELSE NULL" part, I just think it shows clearly that I intentionally want to yield a value of NULL.
Ultimately, we will feed in a reference line using this calculation and set the "fill above". So for items in the set, it will yield a value of 0 and fill upward, but for those that are not in the set, it will have a null value...so no reference line at all. So let's do the work.
First, add the Sub-Category Set to detail. Then right-click on it and choose "Show Set". This will give you a set control. Go ahead and check a few random boxes in that set control. Next, add the Custom Row Banding calculation to detail. Now add in a reference line. Set up the reference line as shown below: Per Pane, no lines or labels, and set a color to Fill Above.
And that's it! You can change the selections in the set control and it will change the row banding in your chart. (See the below screenshot). You can preset this yourself for the end user or you can just expose the set control to the end user and allow them to do it themselves!
Now, you might be asking (like Ghafar did), can we band the entire row? As you can see, the Sub-Category headers in the above image are not banded. Well, that's a bit trickier. The reference band technique will work with continuous measures, but you can't really do this with discrete headers. That said, if we are willing to drop the dual axis, then we can do something to ensure that the banding stretches across the entire row.
So, get rid of the dual axis that is creating the line. And I'd recommend adding a white border to the area chart.
Now, create a calculation called Sales - Window Max:
WINDOW_MAX(SUM([Sales]))
Add that field to Columns and dual-axis it with the SUM(Sales) field that is already there. Click our calculation and choose Edit Table Calculation. Set it to Specific Dimensions and check all boxes. This will calculate the window max for everything in the view.
On the Window Max marks card, set the mark to a line, reduce the opacity to 0%, add Sub-Category to the label, then set it to label Line Ends, and to only label the start of the line.
Now, you've added that Sub-Category label within the chart itself. You can now uncheck Show Header for the Sub-Category pill on Columns and you should be left with this:
And that's it! Custom Row Banding for you and your clients.
I want to thank Ghafar first for being a great friend and great person in general, and second, for allowing me to write about this.
Thanks for reading!
No comments: