I came across an interesting challenge the other day while I was working on a client project. Although Tableau already allows users to highlight dimensions from a colour legend, using a colour legend as a filter is a bit trickier. In order to work around this, you can create a custom colour legend as a separate sheet and use that on your dashboard with a filter action (see community post here). However, in order for this hack to work, the field you are using to colour your data needs to be a dimension. What if you are colouring your data by a calculated field?
Let me illustrate the problem with the go-to sample superstore data. Let’s say we want to see a scatter plot that breaks our customers into quadrants based on sales and profit. Something like this:
Next, we colour the individual quadrants using the following calculation:
Wait, whoa, what’s going on here? Why the nested LOD calculations? Why make it so complicated? Well, essentially what we’re doing here is writing a calculation that is the equivalent of WINDOW_AVG. So if we were using WINDOW_AVG, the calculation would look like this:
So why not just use WINDOW_AVG? Hold your britches, explanation on the horizon, about 2 paragraphs down.
So we drag our complicated nested LOD calculation onto the colour shelf of the marks card, and we get to this point where our quadrants are differentiated by colour:
Sweet. Now this is all fine and dandy and we’d like to see a list of our customers based on the quadrants they fall into.
As you can see in the screenshot above, we’ve got a list of our customers with the quadrants calc we used to colour the scatterplot included as a detail. If we create a new dashboard and put everything together, we can see that clicking the colour legend will not filter our customer list, it will only highlight relevant customers.
Using the method outlined in the community post here, we can use a colour legend as a filter by creating a new sheet that mimics the design of a colour legend. Remember how we could have done our quadrant colouring calculation with a simple WINDOW_AVG? Well, this is what happens when we try to create a colour legend sheet with such a calculation:
WINDOW_AVG is a table calculation that needs Customer Name as a level of detail in order to calculate properly. The problem is, we end up with too many squares. The solution? Sets. And sets don’t like table calculations. Hence, the long, drawn-out, nested LOD calc.
To remedy the situation, create Customer Name sets for each of the quadrants, based on the calculation we used to colour our quadrants. For example, a High Sales, High Profit set would look like this:
Once you’ve got all four sets created, drag all of them onto the detail shelf of the sheet where you have your customer names listed. Then, create a new calculation with the following:
In both your scatterplot sheet and customer name list sheet, drag this new calculation onto the detail shelf of your marks card. Create a new sheet and drag the same calculation onto the rows shelf and the colour shelf. You end up with something that looks like a colour legend. Put the whole shebang together on a dashboard, add a filter action between your legend sheet and name list and a highlight action between your legend sheet and your scatterplot, and you get this:
Now you’ve got an interactive dashboard that breaks your customers down into quadrants and gives you a list of those customers depending on your selection. A useful tool for segmentation analysis, and a good way to practice the utility of sets in Tableau.