Tableau Deep Dives are a loose collection of mini-series designed to give you an in-depth look into various features of Tableau Software.

The last two articles of this Deep Dive are going to focus on some advanced uses of sets. We’ll start by looking at how we can use sets in calculated fields. It doesn’t matter if the set was created manually or if the set is based on logical conditions. In either scenario, we can use our set – so long as we use an If/Then statement to test for members IN the set (True) or members OUT of the set (False).

Let’s examine a use case.

Use Case Example

We’ll use the Superstore – Sample data source that comes packaged with Tableau Desktop to construct all of our examples. In our hypothetical superstore, we want to create a sales goal based on our historical performances. We want to increase our sales by +20% in all markets that we have achieved a certain level of penetration, a minimum of 100 customers. This will be the IN of our set.

For the remaining states in which we have fewer than one hundred customers each, I want to instead calculate what our sales would be if we had 100 customers in that state. That’s our adjusted sales goal for those states. In our set, this will be the OUT.

Let’s first create our set where we select states with 100 or more customers.

Now we can use our set in a calculated field that tests for the IN membership to perform one calculation and the OUT membership to perform another. First, we need to use a Level of Detail calculation to determine the average sales by customer. For more on these types of calculations, check out my Deep Dive on LODs.

Now I can build my calculated field using my set:

For the members IN our set, those that have 100 or more customers, we’re going to adjust their sales by +20%. For everything else, i.e. those NOT in the set, we’re going to calculate what the sales would be if they had a minimum of 100 customers.

Now let’s build our view. I’ll bring my Sales Goal calculated field onto Columns along with State and COUNTD(Customer Name) as a discrete field onto rows. Then I’ll build a reference line using SUM(Sales) to compare our actual sales against our goal. Finally, I’ll drag my set onto the Color button and add some notation in the view title to help my user understand our calculations.

That gives me the following result:

The states that are in orange are developing markets (less than 100 customers) and the markets in blue are my mature markets (100 or greater).

What’s Next?

Our final article in this Deep Dive explores how we can use a set within a hierarchy. As always, your comments and thoughts are appreciated in the area below.