Tableau Master Challenge #1 Solution

Home   / Tableau Master Challenge #1 Solution

So you think you’re a Tableau expert? If you’ve arrived here, you must have decided to take our Tableau Master Challenge. If you didn’t arrive here from the challenge, go check it out here to get the details of the challenge and what you have to accomplish to succeed with this challenge.

If you did arrive here from the challenge, you’ll recall that your solution should result with this (reminder, this is based on the 9.3 Superstore demo data):


The solution to this challenge requires a knowledge of the nine types of filtering that Tableau supports. These filters occur in a specific sequence and have varying levels of “secureness”. For this challenge we’re only focusing on the sequence of filters. Specifically, the nine types of filters, in order, are:

  1. Data source filter
  2. Extract filter
  3. Context filter
  4. Dimension filter
  5. Measure filter
  6. Quick filter
  7. Related quick filter
  8. Table calculation filter
  9. Dashboard filter actions

To succeed in this challenge requires you to properly use a context filter, two dimension filters, and a table calculation filter. In addition, you need to use a level of detail (LOD) expression to bypass one of the dimension filters, but respect the other filters. So the proper solution should take advantage of the follow computation sequence:

  1. Eliminate all returned orders
  2. Compute the multi-year average sales per sub-category
  3. Remove any years the user doesn’t want
  4. Remove any sub-categories the user doesn’t want
  5. Eliminate all but the top 3 remaining sub-categories

If you don’t configure the view exactly right, you cannot achieve this sequence. The solution takes advantage of the fact that a FIXED LOD expression is always calculated prior to dimension filters. This essentially allows you to bypass the year and subcategory filters when computing the multi-year average.

If you take a look at the workbook you’ll find the solution. However, here are the steps you can use to recreate the solution on your own.

Create the solution

  1. Create a new data source based on the Superstore Sales 9.3 demo Excel file and include the Orders and Returns tables, using a left join from Orders to Returns
  2. Create an LOD expression calculated field named Sub-Category Average Sales with the formula: { FIXED [Category], [Sub-Category] : AVG( [Sales] ) }
  3. Compute the percentage variance in another calculated field with the formula: ( SUM( [Sub-Category Average Sales] ) – AVG( [Sales] )) / AVG( [Sales] )
  4. Create a table calculation calculated field that computes the rank based on the Sub-Category average sales, with the formula: RANK_DENSE( AVG( [Sub-Category Average Sales] ))
  5. Add Order Date to the columns shelf using a continuous Year date value
  6. Add average Sales and sum of Sub-Category Average Sales to the measure values
  7. Move Measure Names to the column shelf
  8. Add Category, Sub-Category Rank, and Sub-Category to the rows shelf
  9. Change sub-category rank to a discrete value, move it into the second position, and set the addressing to pane down
  10. Drag the Returned dimension to the filter shelf and exclude null values
  11. Add the Returned filter to context
  12. Show filters for the year of Order Date and Sub-Category
  13. Add the Sub-Category Rank measure to the filters shelf, set the addressing to pane down, and filter to only show values 1, 2, and 3
  14. Turn off the Sub-Category Rank header
  15. Unselect 2011 and 2012 Order Date year
  16. Unselect binders, bookcases, envelopes, fasteners, paper, and supplies in the Sub-Category filter

If you follow these steps exactly you should arrive at the correct answer, which is:


If you arrived at this answer, then congratulations, you have beaten the Tableau Master Challenge! If you didn’t arrive at this answer, try to figure out which step may have thrown you off. There are a couple of “gotchas” in this challenge that you might have missed, including:

  • Add the “Returned: Null” to context
  • Create a FIXED LOD expression to “bypass” the Order Date year filter and arrive at the complete multi-year average
  • Set the addressing on the Sub-Category Rank to pane down
  • Change the Sub-Category Rank measure to a discrete value

Hopefully you enjoyed this challenge and learned something from it. Filtering seems like a simple, straightforward concept, but there’s a lot more to it than just the filter shelf in your view. If you found this challenging, take a look at our Tableau Master Class training course. We offer two day training that address topics like this, as well as other advanced Tableau concepts.

Want to know more?

Contact us