We’ve shown you before how scatter plots create a natural four quadrant segmentation to make analyses more actionable. Now we share how to use a combination of calculated fields and dashboard actions to zoom in and out those quadrants – all on a single sheet!
Calculated field that creates four scatter plot segments:
IF SUM([Sales]) >= WINDOW_AVG(SUM([Sales])) AND SUM([Profit]) <= WINDOW_AVG(SUM([Profit])) THEN “A”
ELSEIF SUM([Sales]) >= WINDOW_AVG(SUM([Sales])) AND SUM([Profit]) >= WINDOW_AVG(SUM([Profit])) THEN “B”
ELSEIF SUM([Sales]) <= WINDOW_AVG(SUM([Sales])) AND SUM([Profit]) >= WINDOW_AVG(SUM([Profit])) THEN “C”
ELSEIF SUM([Sales]) <= WINDOW_AVG(SUM([Sales])) AND SUM([Profit]) <= WINDOW_AVG(SUM([Profit])) THEN “D”
Hi, this is Ryan with Playfair Plus. And in this video, I’m going to show you how to make a zoomable scatter plot in Tableau that allows you to drill in or zoom in to a single quadrant at a time, as well as zoom back out to the original view all within the same worksheet. By the end of this video, you’ll be able to make a scatter plot similar to what you see on the screen here. This can be built with any measures. I’m just using Sales and Profit from the Sample Superstore data set.
But notice there’s a reference line drawing the average on the y-axis and an average on the x-axis, and I’ve written a formula that buckets each of those quadrants into their own category. I’ve just called those A, B, C, and D, but you can call those whatever you’d like. The top right quadrant is delivering the highest sales, as well as the highest profit. And if I were wanting to drill in to figure out– take a closer look at that particular quadrant, I could click any of the dimension members and Tableau will zoom in to just that particular quadrant. If I click the Escape key or click the same dimension member that I clicked to zoom in, the chart zooms back out to the original view.
So, to get started over here, I’m going to open a new version of Tableau and just start from scratch. The first thing we need is the scatter plot. This is beyond the scope of this video, so I’ll move very quick. We do have other resources to show you how to do this.
But the quick version is I’m going to double click Sales and double click Profit. That gives me a scatter plot with one circle at the combination of sales and profit. I’m going to change the mark type to Circle. Make that circle a little bit larger so we can see it. And I’m going to change the level of detail of this view to Sub-Category, which is in this Product hierarchy. I will drag Sub-Category to Detail, which will give us 17 circles instead of one.
Alright, the next step, and we also share this on another video and in a blog post called, Three Ways to Make Stunning Scatter Plots in Tableau, so I’m not going to have you sit here and watch me type. I’m going to cheat a little bit on this one and copy the formula. But what this formula does is creates that dynamic segmentation where I’m throwing everything that’s above average in both axes into one quadrant. Those that are above average in sales, but below average in profit in a second quadrant. Those that are both below average in sales and profit in the bottom left. And then that leaves me with sub-categories, in this case, that are above profit– above average in profit, but below average in sales.
So that’s what this formula does, but we will just share it in the related content below the video. But I’ll call this my Scatter Plot Segment. And that is the entire formula. I’ll click OK. I will drag that to Color. What you’ll notice at first, it doesn’t provide the four different colors. I just see everything has been segmented into scatter plot segment A. That’s because the calculated field that I just made leverages a table function called Window Average, and I know that there’s a table calculation taking place within that Scatter Plot Segment calculated field because there’s a delta symbol on that pill.
What’s happening is, by default, the table calculation is computing from left to right across the table. But I need to change the addressing– so that it addresses at the Sub-Category level, which will redo the colors, and we should see four colors appear. I can edit the compute using or addressing of the table calculation by clicking into a field that has a delta symbol on it. Hover over Compute Using, and choose Sub-Category instead of Table Across. Now my four colors appear. I could also quality check that that calculation was throwing each quadrant into the right segment by navigating to the Analytics pane and putting an Average reference line on both the x-axis, as well as the y-axis because we shouldn’t see any overlap. There should be one color per quadrant.
So that’s the foundation of this zoomable scatter plot. The trick to making this go is we will use a combination of dashboard actions to get this to zoom in and out, and specifically we will eventually use a Parameter dashboard action. So the first thing I need to do is create the parameter that the dashboard action will be based on. So I will create a parameter. I’ll call this one my ‘p’ dot Scatter Plot Segment. I always just put a ‘p’ preceding the names of my parameters to remind me that is a parameter.
The data type, in this case, is String. I have a very specific list of choices. So I will click the List radio button and type out what each of those choices should be. I’ve got A, B, C, D, and then I’d like to make a global reset option. So I’ll also put ‘All’ as the fifth allowable value. But that’s the entire parameter. From the first step, I’ll click OK.
The next thing we need is a calculated field that will act as our filter, so I’ll call this Scatter Plot Filter. And this will be based on the value of the parameter we created in the first step. And I like to make these filters Boolean, it makes them process very efficiently. They’re also relatively easy to write out the logic. So the formula this time will be p dot Scatter Plot Segment equals the Scatter Plot Segment calculated field, or p Scatter Plot Segment equals ‘All’. That’s my global reset button. But that is the entire formula. Again, I’ll click OK.
Just to show this is working, I’m going to show the parameter control. I also wanted to go ahead and set the current value of the parameter to All, so that when I initially add this filter, nothing happens until I code the parameter dashboard action. So the current value is All. I will place the Scatter Plot Filter onto the Filters shelf. I will choose True. Reason this is true is remember our logic is saying that this parameter can also equal the value of All. And because it equals All at the moment, my only option is True because the logic is being met. But that’s perfect, that’s exactly what I want. So I’ll click OK.
We don’t see anything filter on the View. But to test that this is working, I can change the current value of the parameter to one of the individual quadrants, and it sure enough does zoom in to that quadrant. I’ll go back to All. But to make this work on click, we’re going to tie a parameter action to the current value of the parameter.
Normally, at Playfair Plus, we do this with Dashboard actions. But in case you weren’t aware, you can also add actions to individual worksheets. They act very similarly to Dashboard actions, but they’re technically called Worksheet actions. And you can add them by navigating to Worksheet in the top navigation and choosing Actions. You have the option to roll this out or view actions from the entire workbook. We’re just working on this one individual sheet, so I’ll stick with the Sheet radio button.
Choose ‘Add Action’. This is a parameter dashboard action, so I’ll choose ‘Change Parameter’. We are on the source sheet, so no change is needed there. We want the action to run on select, so we’ll leave that radio button intact. The target of our action is the p dot Scatter Plot Segment, so I will choose that parameter. And the source field, the thing that we are passing to the current value of the parameter is the Scatter Plot Segment.
And then lastly and very important, this is what gets the reset to work. When you clear the selection, which you can do by either clicking the Escape key or clicking the same dimension member that activated the action, we want to set the value to All. That’s what’s going to overwrite the current value of the parameter, go back to All and reset the view. That is the entire parameter. We could give this a better name if we wanted to. I’ll call this Scatter Plot Zoom, and click OK. Click OK again.
Go to Presentation mode just to take away the Authoring interface. And now if I click on an individual mark, it zooms into that quadrant. If I click the individual mark again, it zooms back out. This is a nice little workaround and hack. I have noticed one little strange bug that I can’t explain. But notice I clicked on a mark that was turquoise, but once it zooms in it turned blue. To alleviate that, you have to go remap one of the colors.
So if I– let’s just double check that we’ve got turquoise set to segment C and go try this again. Well, it redid all my colors, didn’t it? So let me reset these. I’ll assign a palette once. Click Apply. Make sure that change stuck. Now if I go back to Presentation mode and pick a turquoise circle, it stays intact. So one little extra step we have to take just remapping the colors. But now that single sheet zoomable scatter plot is intact.
One more tip for you. I really like this functionality, but there’s one little user experience thing I don’t like about it. When I click into an individual quadrant, notice that it’s highlighting the one mark that I clicked on and everything else is kind of grayed out. I have another workaround for that, which is to create a calculated field that I always just call, ‘Don’t Highlight’. And the entire formula is, quote, “Don’t Highlight.” And I will put that onto the Detail property of the Marks card and add one more worksheet action. This time it is a highlight dashboard action.
Again, because it’s a worksheet action, we’re already in the right spot. We’ve got the right source, the right target already selected. But the important piece of this trick is you have to specifically select the Don’t Highlight field that we just created. And the reason that this works is when I added Don’t Highlight to Detail, that’s technically part of the detail of all of the marks so it’s the data is there throughout the view. And the reason this will work is I’m setting up a highlight action to where if I click Don’t Highlight, it will activate that don’t highlight data but on every single mark. So every single mark will be highlighted. Kind of a way to fake Tableau out.
So I’ll click OK to close this. Click OK again. Go back to Presentation mode one more time. This time you’ll see when I click on the turquoise, all of the other turquoise circles are still highlighted. If I click either the same data point again, or the Escape key, it will zoom back out to my original view.
This has been Ryan with Playfair Plus – thanks for watching!