How to Use a Tableau Set Control for Scenario Planning

Multiple What-If Outcomes on a Single Tableau Worksheet

Ryan shows you how to use the Tableau set control that was introduced in Tableau Desktop 2020.2 to do advanced what-if scenario planning. You’ll learn how to do multiple what-if analyses on a single view, color marks by whether they are included in a scenario, and create a dual-axis bar-in-bar chart to visualize outcomes.

Calculated field to compute what-if sales:
IF [Positive Set] THEN [Sales]*(1+[Positive Scenario Parameter])
ELSEIF [Negative Set] THEN [Sales]*(1+[Negative Scenario Parameter])
ELSE [Sales]
END

Calculated field that colors marks based on whether they are included in the scenario analysis:
IF [Positive Set] THEN “Positive”
ELSEIF [Negative Set] THEN “Negative”
ELSE “Other”
END

Calculated field that creates a bar in bar chart showing the difference between sales and what-if sales:
SUM([Sales]) – SUM([What-If Sales])

Hi, this is Ryan with Playfair Data TV, and in this video, I’m going to show you how to use a set control in Tableau, which came with the introduction of Tableau Desktop 2020.2, and I’m also going to show you how to use a set control in a real-world application, and we’re going to use it to do better what-if scenario planning in Tableau.

I’ve shown you, in the past, how to do a very simple what-if analysis in Tableau using parameters. I’ll share that in the related content below the video, but set controls make this type of analysis much easier to do, because you can choose which dimension members are included in an analysis, and you can even have multiple what-if scenarios on the same view.

So that you can follow along using the Sample Superstore dataset, I’m going to throw out a use case using the sample data where we’ll say we want to look at sales by sub-category and apply two different scenarios to the dimension members on the view. We’ll allow the user to change the scenarios for sales to see what would happen if sales increased, as well as what would happen if sales decreased.

So that we can see this, I will double-click on Sales, and double-click on Sub-Category, just to get us started with our baseline view. I’m going to swap the Rows and Columns Shelf and fit the entire view. And so that we can see and check the math later on, I’ll turn the labels on.

All right, to create these multiple what-if scenarios, we need four things. The first two things we need are a set for the positive scenarios and a set for the negative scenarios. The easiest way to create a set is to right-click on the dimension that you want to create the set from– so in our case, sub-category, because we’re choosing different sub-categories to include in either the positive or the negative set. Then hover over Create, and click Set. And I will call this my Positive Set.

I’m going to leave it empty for now, because we will control the dimension members included in this set using Tableau’s Set Control. So for now, leave it blank. Click OK. Now that I have the first set, I can right-click on the set and click Duplicate, and then I can edit that set and just rename it Negative Set. Click OK.

The third and fourth things we need are the parameters that control the magnitude of these scenarios. Again, we need both a positive, as well as a negative scenario. So I will start by creating the parameter for the positive values.

The way that I like to create a parameter is to right-click in any blank space on the Data pane, and click Create Parameter. And I will call this the Positive Scenario. I’ll leave the data type as Float, but I’m going to change the display format to Percentage with no decimals.

This is a little bit of a pitfall. Just keep in mind that when you choose a display format of Percentage, the number will automatically be multiplied by 100%. Because I have a very specific range of values in my mind anyway for this use case, I’m going to click Range. And just to put some parameters around these scenarios, let’s say that, for the positive scenarios, the user can decide to see what would happen to sales if we increased them from 0% to 100% in increments of 5 percentage points.

For the negative scenario, we’ll say that they can decrease the sales, or see what would happen if they decreased the sales, from 0% to minus 100% in increments of 5. For that scenario or use case, my minimum would be 0. My maximum would be 1.

Remember, percentage display format is multiplied by 100, so 1 is actually a 100% increase. And then my step size would be 0.05. That’s the same thing as 5%. So positive scenario– 5%, 0% to 100% is the total range. Click OK.

Now that I’ve made the first one, I can right-click, duplicate it, edit it, and call that my Negative Scenario. This one is going to go from 0% to minus 100%– so minus 1. I’ll keep the increments as 0.05. So far so good. Let me call this Negative Scenario, and click OK.

All right, now that we’ve created those four things, we need a calculated field that really makes this whole thing go. This is going to be our What-If Sales. It’s going to incorporate both the positive and the negative scenario, and apply it to the correct dimension members– whether the dimension members are in the Positive Set or the Negative Set.

I will start a new calculated field. I’ll call this What-If Sales. And the formula is IF the dimension member is in the Positive Set, then take Sales times 1 plus my Positive Scenario, close parentheses. I will share this in the Related Content below the video. It does get just a little bit tricky with how you’re multiplying percentages, but we are on the right track so far. So I’m going go to the next line, click ELSEIF.

If it’s in the Negative Set, then I want to take the Sales times 1 plus the Negative Scenario. And if you’re not in either one of those sets, I’m going to do a catch-all here at the bottom that says ELSE Sales. So I’m not going to have either the positive or the negative scenario applied. And then if/then logic always concludes with the word END.

So that is the entire formula. I’ll go ahead and click OK. I’m going to add this to the right side of this chart by dragging the newly-created What-If Sales to the Columns Shelf, and this way I can do some quality assurance and check the math.

This is what I would expect so far. Both the left side and the right side look exactly the same. It’s because we haven’t added any dimension members to either one of our sets, nor have we changed the Positive Scenario or the Negative Scenario parameter levers. So that we can check some of these things out, I’m going to show both the set controls, and both those parameter controls.

So first the new part of this– I’m going to show what’s called a Set Control for the Positive Set. This did not exist prior to Tableau Desktop 2020.2. Before Tableau Desktop 2020.2, you had the option to show a filter for the set, which would just tell you or allow you to control whether a dimension member was in or out of the set, but now– click Undo– what I can do is right-click on the set, and there’s a new option called Show Set.

What this one allows me to do is in addition to choosing or determining whether the dimension member is in or out of the set, it allows me to actually control which specific dimension members are either in or out of the set. One nuance to this new set control is Tableau doesn’t allow you to show it unless that set is somewhere on the view.

Well, fortunately, that set was already incorporated in my What-If Sales calculated field. That’s why I already had the option to show the set. If you are not using a set on the view, that option will be grayed out, and Tableau will tell you that you have to add that set to the view in order to access the set control.

I’m going to do the same thing for my Negative Set– show the set control. And I’m also going to put the positive version on top. I think that’s a little bit more intuitive to the user.

So what we’re doing with these two set controls is determining which dimension members from the Sub-Category dimension should be included in this scenario or what-if analysis. To control the magnitude of that analysis, that’s where our parameters come in. I want to show both the Positive Parameter value, as well as the Negative Parameter value. I’m also going to set both of these at 0 for the default current values.

Now to do some quality checking. I’m going to click Accessories on the Positive Set– which adds it to the set, but we still don’t see anything change. That’s because the magnitude of the positive what-if analysis is also 0. If I bump this up 5%, we should see the number change. And just like all the other calculations I like to do in Tableau, I like to quality check some of this.

So let’s check out Accessories. I’m going to take 175,749 divided by 167,380, and sure enough, that is a 5% increase. But now, here’s what’s so nice about a set control. I can choose to apply that positive 5% change to multiple dimension members at the same time by simply clicking their dimension members here in the set control.

I can do the same thing for the Negative Set. Maybe I’ll go to the next three– Binders, Bookcases, and Chairs– and for the negative scenario, I’ll bump that down by 25%. Let’s quality check one of these as well.

So for Binders, our what-if scenario– we ended up at 152,560 divided by the original value– 203,413– and sure enough, 0.75, or 75%, represents a decline of 25% in this case. So we actually already have this scenario planning working. We’re doing multiple scenarios at the same time, both positive outcomes and negative outcomes.

We’re using a set control to tell Tableau which dimension members should be included in this analysis. So this is already getting very powerful, but I’m going to give you a couple of extra tips and ideas to make this even better. One thing I would really like to know is, which of our dimension members are included in the scenario planning?

To do so, I’ll need another calculated field, and I’ll call this Included in Scenario. And I’m going to classify each dimension member as either being included in the positive scenarios, the negative scenarios, or no scenarios. And I’m going to eventually color the marks based on whether it’s being analyzed in this what-if analysis.

The formula in this case would be IF the Positive Set, then I’ll just call that Positive. That’ll eventually generate one color. ELSEIF the Negative Set, then we’ll call that one Negative. ELSE, which is kind of a catch-all– we’ll say Other, and then END.

That is the entire formula. I’ll click OK, and drag that to the Color Marks Card for the All Marks Shelf, which controls all of the marks on the view. So I’ll drag that to Color. Sure enough, we see three colors appear.

Maybe I will remap those colors by double-clicking on the color legend, just to give these some more intuitive colors for the user. Negative should probably be red. Other I’ll make gray, and positive I’ll make blue. And click OK.

Now if I add an additional value to the Positive Set further down my rows, like Furnishings, we should see that bar turn blue, and we do. If I add a dimension member further down for negative, such as Tables, we should see that bar turn red, and we do. One nuance of IF/THEN logic that I would like to point out is it’s processed in order, and once a dimension member is classified as one thing, it can’t be classified as a different thing.

So you might have been wondering, what would happen if you’re in both the positive and the negative set? Because after all, you can’t have both a positive change and a negative change at the same time. So what is happening is– and let me actually open this formula so we can take a closer look on What-If Sales.

And just to give us a better example, let me add Appliances to the Negative Set. If I click Appliances, notice the color did not change. That is because of what I’m trying to explain here, which is once a dimension member is classified as being in the Positive Set, it gets Sales times 1 plus Positive Scenario applied to it before Tableau even makes it to that second line to use the Negative Scenario. It can only be classified as one thing at a time, and it’s always the first classification.

So you don’t have to worry too much about overlap. In this case, what’s happening is the positive scenarios are always overriding the negative scenarios if you ever had a dimension member in both the Positive Set and the Negative Set.

All right, this is looking pretty good, but just one optional way to enhance this chart is– and I should point out that I’m using a bar chart for simplicity, and I had to pick a use case to show you in this video tutorial, but you can take this same type of logic and apply it to any chart you would like.

So take it or leave it, but I’m going to show you one more tip on how you might enhance this what-if analysis. What I’m going to do is leave the left side as is with a baseline bar chart, but then I’m going to make a dual-axis combination chart, and on the other axis, I’m going to show either the positive or the negative change.

Well, if you were listening closely in that use case, there is one more calculated field we need, because we don’t actually have the difference between our newly-generated What-If Sales minus our original Sales values. Or actually, it’s the other way around, but let me show you how to make that formula.

So I’m going to create a calculated field. I’ll call this Sales minus What-If Sales. And the formula would be SUM of Sales minus SUM of What-If Sales.

I did say that backwards. I’ll share this in the Related Content below the video to help avoid confusion. But this is eventually going to generate the size of the dual axis. I will click OK.

First thing I need to do is convert this chart– this two-column bar chart– into a dual-axis combination chart. Of course, we have you covered here at Playfair Data TV and show you several ways to do this on a separate video. For this video, I’ll use the technique of clicking on the second pill on the Columns Shelf, and clicking Dual Axis.

Quite a bit going on here. Tableau tried to help us out by not having too much stuff overlap, but I’m going to clean it all up by navigating back to the All Marks Shelf and getting rid of all my coloring and all of my labels. And the first thing I’m going to do is get the primary axis– so it’s this first Marks Shelf– back to Bar. That’s our default baseline that we had already seen.

I’ll also change the color to maybe a grayish color. On the right side– so the second Marks Shelf with a mark type on it– I’m going to change the mark type from Circle to Gantt Bar. I’m also going to reset all of our scenarios.

I’m going to get rid of everything in the sets, and I’m going to change both the positive and negative values to 0. It looks like these charts are synced up. Just to be positive, I’ll right-click on the axis, and click Synchronize Axis. You can do that on either axis.

That just ensures that my Gantt bars are lined up perfectly with the end of where each default or baseline bar ends. So far so good. To size those Gantt marks, I’m going to use my newly-created Sales Minus What-If Sales by dragging it to the Size Marks Card. We don’t see anything change yet, because we don’t have anything in our scenarios yet.

I’ll click Accessories and bump that up by 20% so we can see that Gantt bar start to extend out. I’m going to make that Gantt bar a little bit shorter, because if we have any negative scenarios, it would overlap with that baseline bar. Let me show you what I mean by that.

I’ll click Appliances and bring that down by 25%, and you can see it moving to the left, over that baseline bar. Well, if I didn’t reduce the size or the tallness of that Gantt mark, it would be directly overlapping with the default bar– not as effective. It would look just like a stacked bar chart.

One more thing I’ll do to enhance this is get my coloring back for which scenario the dimension member is being included in. I can do that by dragging the Included in Scenario dimension to the Color Marks Card. Now, if you’re in the Positive Set, you’re colored blue. If you’re in the Negative Set, you’re colored red. And everything without a scenario applied to it is colored gray.

I’ll just add a couple more scenarios in here. I’ll throw Binders and Bookcases in. As you can see, they’re getting that same 20% increase. I’ll go a little bit further down, add Envelopes, Fasteners, and Furnishings to the Negative Scenarios, and those are getting the negative 25% applied to them.

So just to recap what we’ve covered here, we are now able to enhance our scenario planning capabilities in Tableau due to this relatively new feature that came in Tableau Desktop 2020.2 called a Set Control. Now not only can my users determine what is included in a set or not, or included in a what-if analysis or not, but they can also do multiple scenarios on the same view.

These are very powerful types of analyses that help us forecast and look forward and present what would happen in our business if we applied these different scenarios to different dimension members.

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