Two Ways to Dynamically Display the Top N vs Other in Tableau

A tactic for improving the focus of an analysis

Reduce the cognitive load on your end users and focus on what matters most by using parameters or set actions to determine which dimension members are displayed, and which are grouped into their own bucket of long-tail data points.

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you a technique that allows you to group your Top N of any dimension and compare it to Other. So we’re going to have two different groups: if you’re in the Top N, we’re going to display the dimension member; otherwise, it’s going to be grouped into a bucket called Other.

We’re then going to take this a step further and use Tableau Set Actions to allow your end user to determine the Top N by just selecting marks on a view. To show you how this is done and also show you why this might be needed in your business, let’s look over here in Tableau Desktop, where I’ve built a chart that looks at the Sales measure by the Sub-Category dimension. I often don’t care too much about what I call the ‘long-tail’ marks. So that’s these down here at the bottom. If I’m an analyst– and I’ve mentioned this in other videos, but I’m a big follower in the 80-20 rule that states 80% of outcomes are due to 20% of effects.

I like to focus on that 20%. So I just want to know, what’s really driving the outcomes in this business? I’d still like to see the context of the others, but they’re not as important. They’re kind of muddying up the view right now.

They’re also increasing the cognitive load. It’s making it harder for my end user to process what’s happening. So that’s why you might want to use this technique.

The first thing we’re going to do in the first method is set up a parameter that determines what the Top N is. So I’m going to create a parameter. And I’ll call this my Top N.

And we’ll make it whole numbers as the data type. And I’ll provide a range of options. This is purely flexible. If you’re new to parameters, make sure you check out the video An Introduction to Tableau Parameters here at Playfair Data TV.

But we’ll just make up some arbitrary range here. We’ll say the Top N can be 5 up to 15 and multiples of 5. I’m going to click OK.

I now need a set that determines whether you’re in the Top N or out of the Top N. So I’m going to create a set by right clicking on the dimension that I want to create that set from– so Sub-Category, in this case, hover over Create, and click Set.

And I’ll just call this Top N Sub-Categories. Go over here to the Top tab, click By Field, and instead of having it be the default top 10, I’m going to change that to our new parameter, Top N. That way this is dynamic. You can change it from 5, to 10, to 15.

I still want that Top N to be based on SUM of Sales. So, so far so good. This is the set that I want. I’m going to go ahead, and click OK.

I will put this onto the Rows Shelf so that we can see how these sub-categories are being broken up. The current value in my Top N parameter is 5. So it put the first 5 dimension members in the set. Everybody else is out of the set.

The next thing we need to do is make a calculated field that will group all of the dimension members that are outside of our set into a bucket called Other. So I’m going to call this Top N Versus Other. And the formula is, IF you are in this set– so Top N Sub-Categories– then I want you to show the Sub-Category name; otherwise, I want you to say “Other” END.

And I could have wrote this a little more elegantly with the Immediate IF function, but I just wanted to kind of show you how this is a little more intuitive about what it’s doing. It’s just saying, if you are in the set, show the name of the Sub-Category; otherwise, say the word “Other.” And I’m going to click OK.

And I’m now going to replace my original Sub-Category dimension with that newly-created Top N Versus Other. Now if you are in the set, as you can see there, it displays the name of the dimension member. And if you are out of the set, it shows everything else grouped into one bucket. And now, if I sort these again, we see our top 5 there. And then everything else is grouped into a category called Other.

There’s one last step with a parameter. If you want your end user to be able to choose from those different allowable values that we coded in the parameter, you have to show the parameter control by right-clicking on the parameter and clicking Show Parameter Control. There’s the Top N parameter.

5 is the current value. If I click this to the right once, we’ve got the top 10. And then everything else is in that bucket called Other at the bottom. If I click over again, it puts the top 15 in the set. We’re now left with just 2 dimension members that are outside of the set that are making up that Other category.

So this is already good. This is a technique that I have already been using for quite a while. But now that Tableau has Set Actions for dashboards, we can set this up to be controlled by not just the parameter control but by a set action, instead.

We’ve got almost everything that we need in place for this to work. So I’m just going to go over here to a dashboard. I’m going to make a new dashboard and throw my Top N Versus Other onto the view. The only difference with this second approach to the first approach that we need to be aware of in the underlying worksheet is that, in order for a set action to work, we need the name of the dimension somewhere on the view. So in other words, if our set is based on Sub-Category, we need Sub-Categories somewhere on this view.

Well, remember, we replaced that. That’s what was on the Rows Shelf, but we replaced it with that new calculated field. So we actually don’t have Sub-Category anywhere on here. I will add it to Detail, which will get that information in that dimension onto the worksheet, which will eventually drive the set action that we’re about to set up.

I’m also going to bump this back to 5 for the moment. And I will go back to my dashboard just to show you how this is working. I’m going to add a set action, which you can do by clicking Dashboard in the top navigation, click Actions, and Add Action.

The one that we’re looking for is Change Set Values. I’m going to click on that. And I will call this my Top N Set Action– fairly intuitive interface. We only have one sheet. So that’s where the action originates.

We’ve only got one data source. So I’ll click Sample-Superstore. We do have a couple of sets. This particular set action is going to be based on that new one that we created– so the one that does not come with the Sample-Superstore data set. It’s called Top N Sub-Categories.

You can also determine when you want this action to run. We’ll have this run on Select. So my end user has to click on the dimension members in order to get those added to the set. You can also choose what to do when you clear the selection. For this particular user experience, I need to choose Keep Set Values.

So what this set action is going to do is, when I click on dimension members on my worksheet, it’s going to overwrite the current values that are in that Top N Sub-Category set. And remember, that’s what’s driving the calculation that determines whether to show the name of the dimension member or group it into that bucket called Other.

So I’m going to click OK. Click OK. And now let’s say we want to just focus on the top 3. If I draw a box around those top 3 dimension members, Tableau overwrites what was in that Top N set. Remember, that Top N set, originally– so that first method– we could only choose Top 5, Top 10, or Top 15.

We’re now overwriting that with the set action. We’re looking at the top 3. Because I drew that box around those 3 dimension members, those 3 dimension members were put into the set. That set is driving our calculated field.

If you’re in the set, we see the name of the dimension member. Otherwise, it gets grouped in that category called Other– so two different approaches to dynamically displaying the Top N versus Other, which will help improve the focus of an analysis, and reduce the cognitive load on your end user.

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