Step 5: Drill down into the details

In the last step you discovered that Tennessee, North Carolina and Florida have negative profit. To find out why, you decide to drill down even further and focus on what's happening in those three states alone.

Pick up where your map view left off

As you saw in the last step, maps are great for visualising your data broadly. A bar chart will help you get into the nitty-gritty. To do this, you create another worksheet.

  1. Double-click Sheet 3 and name the worksheet Profit Map.

  2. Right-click Profit Map at the bottom of the workspace and select Duplicate . Name the new sheet Negative Profit Bar Chart.

  3. In the Negative Profit Bar Chart worksheet, click Show Me, and then select horizontal bars.

    Show Me highlights different chart types based on the data you've added to your view.

    Note: At any time, you can click Show Me again to collapse it.

  4. You now have a bar chart again – just like that.

  5. Multi-select the bars on the left by clicking and dragging your cursor across the bars between Tennessee, North Carolina and Florida. On the tooltip that appears, select Keep Only to focus on those three states.

    Note: You can also right-click one of the highlighted bars, and select Keep Only.

    Notice that an Inclusions field for State is added to the Filters shelf to indicate that certain states are filtered from the view. The icon with two circles on the field indicates that this field is a set. You can edit this field by right-clicking the field on the Filters shelf and selecting, Edit Filter.

  6. Click the image to replay it

    Now you want to look at the data for the cities in these states.

  7. On the Rows shelf, click the plus icon on the State field to drill-down to the City level of detail.

  8. Click the image to replay it

There’s almost too much information here, so you decide to filter the view down to the cities with the most negative profit. How will you do this? With a Top N Filter.

Check your work! Watch "Pick up where your map view left off" in action

Click the image to replay it

Create a Top N Filter

You can use a Top N Filter in Tableau Desktop to set a limit to the number of marks displayed in your view. In this case, you want to use the Top N Filter to hone in on poor performers.

  1. From the Data pane, drag City to the Filters shelf.

  2. In the Filter dialog box, select the Top tab, and then do the following:

    1. Click By field.

    2. Click the Top drop-down and select Bottom to reveal the poorest performers.

    3. Type 5 in the text box to show the bottom 5 performers in your data set.

    4. Tableau Desktop has already selected a field (Profit) and aggregation (Sum) for the Top N Filter based on the fields in your view. These settings ensure that your view will display only the five poorest performing cities by sum of profit.

    5. Click OK.

    You can now see that Jacksonville and Miami, Florida; Burlington, North Carolina; and Knoxville and Memphis, Tennessee are the poorest performing cities by profit.

    There is one other mark in the view – Jacksonville, North Carolina – that doesn't belong, because sales in that city are actually profitable. Impostor!

    What is going on here? Why would a city with a positive profit show up in the view when you created a filter that was supposed to filter it out?

    That's a great question, and a great reason to introduce the Tableau Order of Operations.

    The Tableau Order of Operations, also known as the query pipeline, is the order that Tableau performs various actions, such as the order in which it applies your filters to the view.

    Tableau applies filters in the following order:

    1. Extract Filters

    2. Data Source Filters

    3. Context Filters

    4. Top N Filters

    5. Dimension Filters

    6. Measure Filters

    The order in which you create filters, or arrange them on the Filters shelf has nothing to do with the order in which Tableau applies those filters to your view.

    The good news is you can tell Tableau to change this order when you notice something strange happening with the filters in your view, like with the imposter, Jacksonville, Carolina in your view right now. All you need to do is add a filter to context. This will tell Tableau to filter that field first, regardless of where it falls on the order of operations.

    But which field do you add to context? There are three fields on the Filters shelf: Region (a dimension filter), City (a top N filter) and Inclusions (Country, State) (Country, State) (a set).

    If you look at the order of operations again, you know that the set and the top N filter are being applied before the dimension filter. But do you know if the top N filter or the set filter is being applied first? Probably not.

    This is a good place to look for the culprit.

  3. On the Filters shelf, right-click the City field and select Add to Context.

    The City field turns grey and moves to the top of the Filters shelf, but nothing changes in the view. So even though you're forcing Tableau to filter City first, the issue doesn't resolve itself.

  4. Click Undo.

  5. On the Filters shelf, right-click the Inclusions (Country, State) (Country, State) set and select Add to Context.

    The Inclusions (Country, State) (Country, State) set turns grey and moves to the top of the Filters shelf. Another mark appears in the view! Concord, North Carolina. You also notice that Miami, Florida disappeared. That's because Concord actually had lower profit than Miami, but the way the filters were being applied, it was not included.

    You're on to something! But Jacksonville, North Carolina is still in the view. So now what's going on?

    Jacksonville, NC is included because City is the lowest level of detail shown in the view. For Tableau Desktop to know the difference between Jacksonville, NC, and Jacksonville, FL, you need to drill down to the next level of detail in the location hierarchy, which, in this case, is Postcode. After you do this, you can exclude Jacksonville in North Carolina without also excluding Jacksonville in Florida.

  6. On the Rows shelf, click the plus icon on City to drill down to the Postcode level of detail.

  7. Right-click the postcode for Jacksonville, NC, 28540, and then select Exclude.

  8. Postcode is added to the Filters shelf to indicate that certain members in the Postcode field have been filtered from the view. Even when you remove the Postcode field from the view, the filter will remain.

  9. Drag Postcode off the Rows shelf.

  10. Your view updates to look like this:

    Check your work! Watch steps 3-8 in action

    Click the image to replay it

    Now that you've focussed your view to the least profitable cities, you can investigate further to identify the products responsible.

Identify the troublemakers

You decide to break up the view by Sub-Category to identify the products dragging profit down. You know that the Sub-Category field contains information about products sold by location, so you start there.

  1. Drag Sub-Category to the Rows shelf, and place it to the right of City.
  2. Drag Profit to Colour on the Marks card to make it easier to see which products have negative profit.

  3. In the Data pane, right-click Order Date and select Show Filter.

    You can now explore negative profits for each year if you want, and quickly spot the products that are losing money.

    Machines, tables and binders don’t seem to be doing well. So what if you stop selling those items in Jacksonville, Concord, Burlington, Knoxville and Memphis?

Verify your findings

Will eliminating binders, machines and tables improve profits in Florida, North Carolina and Tennessee? To find out, you can filter out the problem products to see what happens.

  1. Go back to your map view by clicking the Profit Map sheet tab.

  2. In the Data pane, right-click Sub-Category and select Show Filter.

    A filter card for all of the products you offer appears next to the map view. This will come in handy in just a second.

  3. From the Data pane, drag Profit and Profit Ratio to Label on the Marks card.

    Now you can see the exact profit of each state without having to hover your cursor over them.

  4. In the Data pane, right-click Order Date and select Show Filter to provide some context for the view.

    A filter card for YEAR (Order Date) appears in the view. You can now view profit for all years or for a combination of years. This might be useful for your presentation.

  5. Clear Binders, Machines and Tables from the list on the Sub-Category filter card in the view.

    Recall that adding filters to your view lets you include and exclude values to highlight certain parts of your data.

  6. As you clear each member, the profit for Tennessee, North Carolina and Florida improve, until finally, each has a positive profit.

    Click the image to replay it

    Look, you found something!

    Binders, machines and tables are definitely responsible for the losses in Tennessee, North Carolina and Florida, but not for the rest of the South. Do you notice how profit actually decreases for some of the other states as you clear items from the filter card? For example, if you toggle Binders on the Sub-Category filter card, profit drops by four percent in Arkansas. You can deduce that Binders are actually profitable in Arkansas.

    You want to share this discovery with the team by walking them through the same steps you took.

  7. Select (All) on the Sub-Category filter card to include all products again.

Learn more: More questions you could ask

You found that binders, machines and tables were responsible for the negative profit in Tennessee, North Carolina and Florida. What now?

You can continue exploring the data by answering the questions below. Note: If you use your view to explore these questions, undo any changes before continuing the Get Started tutorial.

  • How are machine sales in Tennessee, North Carolina and Florida? Are you selling a lot? If so, how is profit impacted?

  • What other factors might be contributing to these results? Are retailers in the south selling products at a discount?

  • What about table and binder sales?

  • What happens to sales in Jacksonville, Miami, Burlington, Knoxville and Memphis when you remove either machines, tables or binders?

  • But what about bookcases? Remember, in Step 2, we saw that bookcases were very unprofitable in other regions. Digging into those regions might reveal additional solutions.

Check your work! Watch "Verify your findings" in action

Click the image to replay it

Now you know that machines, tables and binders are problematic products for your company. In focussing on the South, you see that these products have varying impact on profit. This might be a worthwhile conversation to have with your boss.

Next, you'll assemble the work you've done so far in a dashboard so that you can clearly present your findings.

More on working with maps and geographic roles in the Learning Library (in the top menu).

Continue to Step 6: Build a dashboard to show your insights.

Thanks for your feedback!