Top N filters in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the top 5 products by total sales for the last quarter to focus marketing efforts.

📊 Data: You have sales data including Product Name, Sales Amount, and Sale Date for the last year.

🎯 Deliverable: Create a Tableau dashboard showing the top 5 products by total sales for the last quarter using a Top N filter.

Sample Data

Product Name	Sales Amount	Sale Date
Alpha	1200	2024-03-15
Beta	850	2024-03-20
Gamma	1500	2024-02-10
Delta	700	2024-01-25
Epsilon	950	2024-03-05
Zeta	400	2024-03-18
Eta	1300	2024-02-28
Theta	600	2024-03-22
Iota	1100	2024-01-30
Kappa	500	2024-02-15

1

Step 1: Connect Tableau to the sales data source containing Product Name, Sales Amount, and Sale Date.

No formula needed; just connect the data source.

Expected Result

Data source is connected and visible in Tableau.

2

Step 2: Create a calculated field to filter sales for the last quarter (Jan-Mar 2024).

Create calculated field named 'Last Quarter Sales' with formula: IF DATEPART('quarter', [Sale Date]) = 1 AND DATEPART('year', [Sale Date]) = 2024 THEN [Sales Amount] ELSE 0 END

Expected Result

Calculated field 'Last Quarter Sales' correctly identifies sales in Q1 2024.

3

Step 3: Create a new worksheet and drag 'Product Name' to Rows and 'Last Quarter Sales' to Columns.

Drag fields as described; set aggregation of 'Last Quarter Sales' to SUM.

Expected Result

Bar chart shows total sales per product for Q1 2024.

4

Step 4: Apply a Top N filter to show only the top 5 products by total sales in the last quarter.

Right-click 'Product Name' in Rows, select Filter > Top tab, choose 'By Field', Top 5 by SUM of 'Last Quarter Sales'.

Expected Result

Only the 5 products with highest total sales in Q1 2024 are displayed.

5

Step 5: Format the chart with clear titles and labels for accessibility and clarity.

Add chart title 'Top 5 Products by Sales - Q1 2024', label axes, ensure color contrast is good.

Expected Result

Chart is easy to read and understand.

6

Step 6: Create a dashboard and add the worksheet to it for presentation.

Drag the worksheet onto a new dashboard, adjust size for readability.

Expected Result

Dashboard shows the top 5 products by sales for the last quarter clearly.

Final Result

Dashboard: Top 5 Products by Sales - Q1 2024

Product Name | Total Sales
--------------------------
Gamma        | ██████████████ 1500
Eta          | ████████████ 1300
Alpha        | ██████████ 1200
Iota         | █████████ 1100
Epsilon      | ████████ 950

(Note: Bars represent relative sales amounts)

✓Gamma is the top-selling product in Q1 2024 with $1500 in sales.

✓The top 5 products together account for the majority of sales in the last quarter.

✓Products outside the top 5 have significantly lower sales.

Bonus Challenge

Add a parameter to let users select the number N for the Top N filter dynamically.

Show Hint

Create a parameter for N, then use it in the Top filter by selecting 'By Field' and referencing the parameter value.

Practice

1. What is the main purpose of using a Top N filter in Tableau?

easy

A. To remove all null values from the dataset

B. To sort data alphabetically

C. To display only the highest ranked data points based on a measure

D. To change the color of the chart dynamically

Solution

1. Step 1: Understand the concept of Top N filter

   A Top N filter is used to show only the top items based on a ranking measure, such as sales or profit.

2. Step 2: Identify the purpose in Tableau

   It helps focus on the most important data points by filtering out lower-ranked items.

3. Final Answer:

   To display only the highest ranked data points based on a measure -> Option C

4. Quick Check:

   Top N filter = show top ranked data [OK]

Hint: Top N filters show only the best items by rank [OK]

Common Mistakes:

• Confusing Top N filter with sorting
• Thinking it removes nulls instead of filtering by rank
• Assuming it changes visual styles only

2. Which of the following is the correct way to create a Top N filter using a calculated field in Tableau?

easy

A. SUM([Sales]) > [Top N Parameter]

B. RANK(SUM([Sales])) <= [Top N Parameter]

C. IF [Sales] = MAX([Sales]) THEN 1 ELSE 0 END

D. INDEX() = [Top N Parameter]

Solution

1. Step 1: Understand the use of RANK() function

   The RANK() function assigns a rank number to each row based on a measure like SUM([Sales]).

2. Step 2: Apply the Top N filter condition

   Filtering with RANK(SUM([Sales])) <= [Top N Parameter] keeps only the top N ranked items.

3. Final Answer:

   RANK(SUM([Sales])) <= [Top N Parameter] -> Option B

4. Quick Check:

   Use RANK() with <= Top N [OK]

Hint: Use RANK() with <= parameter for Top N filter [OK]

Common Mistakes:

• Using SUM([Sales]) > parameter instead of rank comparison
• Confusing INDEX() with RANK() for Top N
• Using equality instead of less than or equal

3. Given the following Tableau calculated field for a Top N filter:

RANK(SUM([Profit])) <= 3

What will this filter do when applied to a sales dataset?

medium

A. Show the bottom 3 products by profit

B. Show all products with profit less than or equal to 3

C. Show products ranked exactly 3 by profit

D. Show the top 3 products by profit

Solution

1. Step 1: Analyze the RANK() function usage

   RANK(SUM([Profit])) ranks products from highest to lowest profit by default.

2. Step 2: Understand the filter condition <= 3

   This condition keeps products ranked 1, 2, or 3, meaning the top 3 products by profit.

3. Final Answer:

   Show the top 3 products by profit -> Option D

4. Quick Check:

   RANK() <= 3 means top 3 items [OK]

Hint: RANK() <= 3 always shows top 3 by measure [OK]

Common Mistakes:

• Thinking it shows bottom 3 instead of top 3
• Confusing rank number with measure value
• Assuming equality means only rank 3, not 1 to 3

4. You created a Top N filter with this calculated field:

RANK(SUM([Sales])) < [Top N Parameter]

But when you set the parameter to 5, it only shows 4 items. What is the issue?

medium

A. The filter should use <= instead of < to include the 5th item

B. The parameter value must be a string, not a number

C. RANK() function does not work with parameters

D. The filter should use > instead of <

Solution

1. Step 1: Understand the filter condition < [Top N Parameter]

   The condition RANK() < 5 includes ranks 1, 2, 3, and 4 but excludes rank 5.

2. Step 2: Correct the condition to include the 5th item

   Changing the condition to RANK() <= 5 will include the item ranked 5.

3. Final Answer:

   The filter should use <= instead of < to include the 5th item -> Option A

4. Quick Check:

   Use <= to include Top N items [OK]

Hint: Use <= to include the exact Top N rank [OK]

Common Mistakes:

• Using < excludes the Nth item
• Thinking parameter type causes issue
• Believing RANK() can't use parameters

5. You want to create a dynamic Top N filter in Tableau that lets users select the number of top products to display by sales. Which combination of features should you use?

hard

A. Create a parameter for N, a calculated field using RANK(SUM([Sales])) <= N, and apply it as a filter

B. Create a filter on SUM([Sales]) > N without using parameters

C. Use INDEX() = N in a calculated field and apply as a filter

D. Sort products by sales and manually select top N in the view

Solution

1. Step 1: Use a parameter to let users select N

   Parameters allow users to input or select a value dynamically, such as the number of top products.

2. Step 2: Create a calculated field with RANK(SUM([Sales])) <= N

   This calculated field ranks products by sales and filters to keep only the top N based on the parameter.

3. Step 3: Apply the calculated field as a filter on the view

   Applying this filter dynamically updates the view based on the parameter value.

4. Final Answer:

   Create a parameter for N, a calculated field using RANK(SUM([Sales])) <= N, and apply it as a filter -> Option A

5. Quick Check:

   Parameter + RANK() filter = dynamic Top N [OK]

Hint: Combine parameter with RANK() filter for dynamic Top N [OK]

Common Mistakes:

• Not using parameters for dynamic input
• Using INDEX() which depends on view order
• Manually selecting top N instead of filtering