Tableau — Using [Measure Values] Twice
If you’ve used Tableau for more than a week or so, and you use a thing called the Internet, you’ve probably seen mention of MIN(1), or placeholder methods for doing some things in Tableau, like complex conditional formatting in tables (if you want some of that, I can offer your 3.5 ways of achieving it, over here), or equally something simple like positioning a label. Well, MIN(1) is last year’s news, this is the year of the Cross 1 Hack.
In this post, I’m going to tell you how to use not one, but two (sorry, no more dramatic than 2, and the title was a bit of a giveaway) instances of [Measure Values]. In reality, this is more likely to be a get-out-of-jail-free card because your data isn’t well structured for Tableau (if you’re new to Tableau, I can explain why, over here, don’t worry that’s the last of those), but we’ve all been there — you have a stacked bar chart that’s made up of two Measures, [Sales] and [Profit]. Awesome. Now you want to add your two lines on the secondary axis, you drag [Discount] to the Rows shelf - looking good — you drag [Profit Ratio] to the rows, right click to make a dual axis and, oh, pook. You can just use two copies of [Measure Names] and [Measure Values], right?, then you can create a dual axis of those. Sadly, no. Measure Values is a one time thing. Or is it? <insert mysterious music>
I wasn’t saying the stuff above to mock you, I was saying it because it was exactly the process I went through, and I’m assuming other people have, too. I’d just like to go on the record and say I would not choose to have a stacked bar and multiple different Measure lines on the same chart, but we all have customers, and that’s all I’ll say about that. That’s a lie, the customers want to cram as much information into that one chart as they possibly can (because more information is better, right?), so they want this — they probably don’t even want a legend:
Look at all that lovely cognitive load, but hey, who am I to judge. I’ve intentionally cropped this image so you don’t get too excited (I sometimes care too much). To get here we use the Cross 1 hack. I call it that because it is essentially a CROSS JOIN (in SQL terms) between two datasets, it allows me to do the same thing lots of times, and I can do them in subtly different ways. “But CROSS JOINS will explode my data!” I hear you cry. This isn’t the case, as Tableau has a concept called “Relationships” (if you take one thing from this post, it should be to never, ever use Blending — you may not know what it is right now, but when it presents itself, walk away). A Relationship creates links between your data, but in simple terms it only does it:
- When it needs to:
If I have two sets of data: one about ticket revenue for seats on planes (I work for an airline, sorry, my examples are dull); and one for the seating capacity of those planes, if I want to draw a chart for the total revenue, Tableau doesn’t require the capacity data, so it doesn’t bother going to that related data and getting any of the information. - After aggregation:
Tableau only crosses the bridge to get the information it needs when it needs to. If I’m looking at seat factor (the percentage of seats filled) overall for each route, Tableau aggregates each side, and then does the join. Envisage some of our data (it’s real, don’t tell anyone or I’ll get fired). If I JOIN the left and right hand tables, in the conventional SQL sense (which you can also do in Tableau, and will be useful in other scenarios) based on the Route field, I will get 10 rows of data which I’d then have to aggregate (and deal with the duplication of capacity on each row):
However, with a relationship, Tableau understands that I am looking at the route level of detail (because I created the relationship using the [Route] field from each side), and aggregates the data up to that level first: JFK = 9 passengers, BOS = 7 passengers. It then crosses the bridge to get the information at that level of detail from the capacity table.
My output is now just two rows, with the correct capacity, and values of 90% and 70% seat factor.
That was a bonus, we don’t care about that right now. Sorry, I’m easily distracted. Let’s create a Cross 1 Relationship.
But First, Scaffolds
I love a good scaffold, they make a lot of things much easier. In case you’re not aware, a scaffold is any auxiliary dataset that provides you with more data to work with. Probably the most common purpose is when you are using dates. Perhaps you don’t have sales for every day, but you want to display every day, or do something else for every date. Creating a date scaffold of all the dates you want, and relating it to your main dataset to use as your “date field” makes the generation of many charts much simpler. Again, not what we’re here for, get on with it, I hear you.
We are creating a scaffold, of sorts, though. Here it is:
Impressive, right? Really, I don’t need column B, but I like to do a bit of shock-and-awe with the visuals, so there there you go, rest your eyes on that for a bit.
From here, the magic begins. I’m building this using Tableau’s Superstore dataset, which comes with 2 relationships already created, between its Orders data, and its People and Returns data. I’ve added a new Connection. I haven’t created a new data source, because that would send us down a dark path towards the B word, which we like to avoid, unless we are unfortunate enough to be working with published data sources (but exciting times are coming on that front, hurrah). My new relationship looks like the image below — click on Add to the right of Connections to add another data source (it doesn’t have to be the same type of data source, you can mix and match SQL queries and Excel files):
The eagle-eyed amongst you may notice the file I have loaded is called “Scaffolds”, because I have an Excel file full of useful little datasets that allow me to do swanky things in worksheets when I need to, everyone should have one. You may also notice the eponymous-ish 1 = 1 condition of the relationship fields. If you really wanted to, you could use any value instead of 1, as long as they’re the same, and call it the Cross “Gavin” Method, but that just makes him sound unapproachable. I’ve also put in more Column Numbers than I need, and more Measure Names than I plan to use because, as you may recall, customers. I like to plan ahead.
The Cross-1 relationship means that, for Column C1, which I have given a name of Profit, I have access to all of the data on the other side of the relationship bridge. All I have to do is specify the Name of the Measure I want to get, and the Value of that Measure (see where we’re going here?).
I’ve worked with the Column Number here, rather than just using the Scaffold Name field, because I can create as many of these calculations as I like, with any combination of Measures, so having hard coded labels might be confusing (sorry if I got it wrong, and this is confusing). For each of those column numbers, I also need to get the equivalent Measures. Tableau is going to do all of the filtering and aggregating in our main dataset (like it did for our flight sales) before coming across to do anything in our scaffold, so we need to make sure that we are only using aggregates on the scaffold side, as well:
Notice that even Column Number needs to be aggregated, or you’ll get the good old, “Cannot mix aggregate and non-aggregate……” we’ve all come to know and love. That is the completion of the new versions of [Measure Names] and [Measure Values].
Where this differs to a normal scaffold is that we haven’t really told Tableau what to look for when it crosses the bridge, like a matching route, or the same date. What we’ve done, though, is said, bring all of your data over to the scaffold side for us to play with, and we’ll choose what we want to display based on the Column Number instead of a matching join field. That’s probably the least helpful part of my entire explanation, but it’s all I’ve got for now. Just go with it, we’re almost done.
We can now take our chart back to simpler times, when it was just a stacked bar, using [Measure Values]:
The first thing to add, is our new [Measure Choice Names] to the filters:
I can choose to display any of the Measures I set up in my calculated field. Obviously, it would make no sense to display those that weren’t on the same scale, but I can use this filter differently on other worksheets, without the need to create more copies. Then, I drag the new [Measure Choice Values] on to the Rows shelf. Tableau will just create one bar, adding up all of the values because, just like [Measure Names], we need to add [Measure Choice Names] to the Colour button. Switching the Mark type to a line gives us this:
I’ve shown everything spread out a bit more (you don’t have to show the user the filter), so that you can see we effectively have a ‘copy’ (not a literal copy) of the [Measure Names] and [Measure Values] fields. The one, key difference between this chart and our original at the top of the page, is that now, instead of 3 pills on the Rows shelf, there are only 2. I won’t spoil it for you — go and make the pills into a dual axis 😉.
