How to Change Sort Order Across Multiple Sheets in Tableau

Change sort order across multiple sheets at once with buttons

In this video, you will learn how to hack Tableau to allow your users to sort multiple sheets at the same time. You will also see how to provide this ability by just clicking buttons on a dashboard!

Hi, this is Ryan with Playfair Data TV. In this video, I’m going to show you how to change the sort order across multiple sheets at the same time in Tableau. Not only that, we’re going to create a really cool user experience that allows the user to change the sort order across those multiple sheets just by clicking on buttons.

There’s two major reasons you’re going to need this tutorial. To show you that, I’ve thrown together these two bar charts that look at Sales by Region and Sales by Ship Mode. As I described on the video, “Three Ways to Make Beautiful Bar Charts in Tableau,” I often like to directly label my marks. It’s more minimalist. It avoids redundant data ink that often comes through the axes. And it’s just a lot cleaner.

One of the drawbacks to that is my end user no longer has the ability to sort these. There is nothing on the axis to click on to change the sort order. Even if I were to go into one of these charts and add my axis back in by clicking Show Header– and let’s go back to our dashboard. So now, I have the ability to sort these in descending order by the measure on that axis.

But watch what happens when I click on this. So I’ll click this to sort in descending order. That’s great. It sorted in descending order. If I click on it again, it changes to ascending order. But nothing is happening to that chart on the right-side. I’d much rather these be in sync, which is what I’m about to show you. So I’m going to undo this just a couple times to get me back to those nice, clean sheets. And we’re going to start this tutorial.

The first thing that you need to do is create a parameter with a data type of Integer and two choices. One will allow the user to sort the bars in descending order. The second will allow the user to sort the bars in ascending order. So I’m going to create a parameter. I’ll call this the Sort Order.

The data type is Integer. You’ll find out why in just a moment. And this is a very specific list of options. So I’ll choose List for the allowable values. And the values should be minus 1 and 1. The values have to be those two integers to get this to work. The Display As, however, can be whatever you would like. And in fact, no one will even see this parameter. So this doesn’t really matter. But just to ground us in what we are looking at, the 1 will eventually represent Descending order. And the minus 1 will eventually represent Ascending order. I’ll click OK.

And now, I need to create two different measures. One measure will be for ascending values. And the other will be for descending values. I’ll start with descending. And the entire calculation will be the aggregation of MIN, and then the integer 1. Whenever I’m making a placeholder– I call these calculated fields placeholders, where there’s just one static number in this calculated field. I always wrap it in the aggregation of MIN just to ensure that that number never changes and that it never gets aggregated in a different way other than MIN.

So this whole calculated field is just always equaling the number 1, essentially. I’ll click OK. We need another calculated field that creates the ascending version of this. And same thing, M-I-N, except this time it’s a negative 1 instead of a positive 1. Click OK. I’m going to start a new sheet, which will be my control sheet for the sort order.

The easiest way to create this sheet is to pre-select your two measures, Ascending and Descending, go to Show Me, create a table. That just gets both of those metrics onto the view. And then change the mark type from Text to Shape. This will allow you to map different shapes for your buttons. One of these shapes will represent a descending sort order. The second shape will represent ascending sort order.

Those labels are muddying up the view. So I’ll turn those off for a moment. And also, I would say that Descending should be on top of Ascending. So I’ll just reorder those as well. And I’ll give us a better name. I’ll call this Choose Sort Order.

And then a couple of other little things I’ll do is I’m going to drag the Measure Names dimension to the Shape Marks Card. That will allow me to choose one shape for Ascending, one shape for Descending. So I’ll put Measure Names on Shape. We’ll see a new palette appear. If I double-click the Shapes legend, I can map new shapes to these. So right now, we’ve got an open circle for Descending and an open square for Ascending. We’re really taking Tableau to the extreme here. Let’s see if it responds and finds all of our shapes.

All right. It’s found the shapes. I’ll move this to Filled. For Descending, I’ll use a positive triangle. And Ascending, I’ll use a downward triangle and click OK– or I’ll click Apply just to preview it. It looks good enough. Click OK. Of course, you can change the colors. I usually like this navy color. And then ‘Descending’ and ‘Ascending’, I could leave those in there as the header to be very explicit about what these buttons are going to do. But I prefer just to assume they’ll figure that out from the title– “Choose Sort Order.” So I’m going to hide that header by right-clicking on it and deselecting Show Header.

All right. There is one more calculated field that we need. And that is to take whatever measure I am sorting by times the Sort Order parameter, so Ascending or Descending. It’s a very simple calculated field. I’ll call this Sort Order. I’ll call this Sales Sort Order, because that’s the metric that I’m using. And by the way, you will need this calculated field for each metric that you are sorting by. That’s the one drawback with this.

In this example, both my bar charts use the metric of Sales. But let’s say I had a third bar chart that looked at Profit. I would need another calculated field called Profit Sort Order. However, the good thing is, whether I’m using one metric or five metrics, all of them are multiplied by the same parameter. So there’s still one parameter that controls all the sort orders. But there are a couple of extra steps if you need to sort across multiple types of measures.

But the whole formula is right there, SUM of Sales times Sort Order. I’ll click OK. And the last thing I need to do before putting this all together is to make sure both of my bar charts on those individual sheets are sorted by this newly created Sales Sort Order calculated field. So I will go into the Sales by Region bar chart, click on the dimension I’m sorting by, and click Sort.

This is very customizable. But we are going to sort by a specific field. And we’re going to sort in descending order by default. And I’m looking for that Sales Sort Order calculated field. So that is the logic for my sort. I need to make sure I do the exact same thing on my second bar chart. So I’ll click on the dimension I’m sorting by, click Sort. It’s a specific field in descending order. And it is my Sales Sort Order.

The reason the aggregation says Custom, by the way, is because we put the aggregation of SUM in that calculated field. Tableau recognized that. You can’t have an aggregate of an aggregate unless you’re using level of detail expressions. So it just says custom. It’s stuck at SUM of Sales. Close that.

Now, we’re ready to bring this together. So on my dashboard– and if you’re following along and doing this in your own business, obviously you need to put all the sheets you’re wanting to sort on the same dashboard. I’d already set that up. But I am going to add that third sheet that we created together. I’m going to add that at the very top. And I’ll make this fit the entire view and make this significantly shorter, maybe center this. Obviously, you can take the formatting as far as you would like. I don’t need this shape legend. So I’ll get rid of that.

All right. And the last step, the trick to making all of this go is we’re going to use the Change Parameter Value dashboard action. And we’re going to say that if you click on one of these triangles, I want you to feed that parameter for Ascending or Descending– that parameter then feeds the calculated field that’s taking SUM of Sales times the Sort Order. And it will magically make both of these charts sorted the same way, just by clicking on these buttons.

So to do this, click Dashboard and Actions. Click Add Action. And we’re going to change a parameter. So that is the one I’m going to choose. And I’ll call this my Changing Sort Order dashboard action. And just set up the logic. This is a pretty simple one. We’re using the Choose Sort Order sheet. If you Select or click on one of the triangles, we’re going to target the value in that triangle.

So remember, those triangles are mapped to two different values. Descending is the integer of 1. Ascending is the integer of negative 1. When they click on either one of those values, I want it to look at the Sort Order parameter and overwrite the Measure Values field, because the measure values are 1 and negative 1.

Aggregation of SUM is fine in this case. Because there’s only one row for each of those, SUM of 1 is just going to overwrite the number 1. SUM of negative 1 is just going to overwrite the value of negative 1. I’m going to click OK, click OK again. And if all goes well, hop into Presentation Mode here. It’s already in descending order. So let’s try to flip it to ascending order. I click Ascending, everything gets sorted by ascending order. Click Descending, everything gets sorted by descending order.

And this would work whether I’ve got one sheet, two sheets as you see here, or 10 sheets as long as they are all using the measure of Sales. And again, what is happening is, when I click on this Ascending button, because I mapped that down triangle to the value of negative 1, when I click on that, that minus 1 is feeding the Sort Order parameter. That Sort Order parameter, now that it has the current value of minus 1, is then feeding the calculated field called Sales Sort Order. And that’s taking Sales times minus 1.

Because we set up the sort order to always be in descending order, when you take Sales (times) minus 1, it flips the best performer to the worst performer, because the highest value, when it’s multiplied by negative 1, it becomes the largest negative value. So we’re just flipping that sort order back and forth. And that’s the trick to sorting multiple sheets in Tableau just by clicking on buttons.

This has been Ryan with Playfair Data TV – thanks for watching!