Rank calculations in Tableau - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the rank of sales in Tableau.

Tableau

RANK_UNIQUE([1])

Drag options to blanks, or click blank then click option'

AMAX([Sales])

BAVG([Sales])

CMIN([Sales])

DSUM([Sales])

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG or MIN instead of SUM causes incorrect ranking.

Forgetting to aggregate sales before ranking.

We use SUM([Sales]) inside RANK_UNIQUE() to rank total sales values uniquely.

2fill in blank

medium

Complete the code to rank sales in descending order.

Tableau

RANK_DENSE([1], 'desc')

Drag options to blanks, or click blank then click option'

ASUM([Sales])

BAVG([Profit])

CMIN([Quantity])

DMAX([Discount])

Attempts:

3 left

💡 Hint

Common Mistakes

Using non-sales fields like Profit or Discount.

Not specifying aggregation before ranking.

Use SUM([Sales]) to rank sales values densely in descending order.

3fill in blank

hard

Fix the error in the rank calculation to rank average profit.

Tableau

RANK([1])

Drag options to blanks, or click blank then click option'

AAVG(Profit)

BSUM([Profit])

CAVG([Profit])

DMIN([Profit])

Attempts:

3 left

💡 Hint

Common Mistakes

Missing square brackets around field names.

Using aggregation without brackets causes errors.

Tableau requires field names in square brackets. Use AVG([Profit]) for correct syntax.

4fill in blank

hard

Fill both blanks to rank sales by category and reset rank for each region.

Tableau

RANK_UNIQUE(SUM([Sales]), [1] = '[2]')

Drag options to blanks, or click blank then click option'

APARTITION BY

BORDER BY

CRegion

DCategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY instead of PARTITION BY for grouping.

Partitioning by Category instead of Region.

Use PARTITION BY Region to reset rank for each region.

5fill in blank

hard

Fill all three blanks to rank profit descending, partitioned by segment, ordered by profit.

Tableau

RANK_DENSE([1], [2] = '[3]')

Drag options to blanks, or click blank then click option'

ASUM([Profit])

B'desc'

CSegment

DPARTITION BY

Attempts:

3 left

💡 Hint

Common Mistakes

Not aggregating profit before ranking.

Confusing ORDER BY with PARTITION BY.

Using wrong field for partitioning.

Rank profit descending, partition by segment using RANK_DENSE(SUM([Profit]), PARTITION BY = 'Segment').

Practice

1.

What does the RANK() function in Tableau do?

easy

A. It sums all values in a column.

B. It assigns a position number to each value based on order.

C. It filters data based on a condition.

D. It changes the data type of a field.

Solution

1. Step 1: Understand the purpose of RANK()

   The RANK() function orders values and assigns a rank number to each based on their size.

2. Step 2: Identify what RANK() does not do

   It does not sum, filter, or change data types; it only ranks values.

3. Final Answer:

   It assigns a position number to each value based on order. -> Option B

4. Quick Check:

   RANK() = position number [OK]

Hint: RANK() numbers items by size, like a race position [OK]

Common Mistakes:

• Thinking RANK() sums values
• Confusing RANK() with filtering
• Assuming RANK() changes data types

2.

Which of the following is the correct syntax to rank sales in descending order in Tableau?

RANK(____, 'desc')

easy

A. COUNT(Sales)

B. AVG(Sales)

C. SUM(Sales)

D. MIN(Sales)

Solution

1. Step 1: Identify the aggregation for ranking sales

   Ranking sales usually uses the total sales, so SUM(Sales) is appropriate.

2. Step 2: Confirm syntax correctness

   RANK(SUM(Sales), 'desc') ranks sales from highest to lowest correctly.

3. Final Answer:

   SUM(Sales) -> Option C

4. Quick Check:

   Use SUM for total sales ranking [OK]

Hint: Rank totals with SUM() for correct order [OK]

Common Mistakes:

• Using COUNT instead of SUM for sales
• Using MIN or AVG which changes ranking meaning
• Omitting aggregation inside RANK()

3.

Given the sales data below, what is the rank of Product B using RANK(SUM(Sales), 'desc')?

• Product A: 500
• Product B: 300
• Product C: 700
• Product D: 300

medium

A. 3

B. 2

C. 1

D. 4

Solution

1. Step 1: Order products by sales descending

   Product C (700) is 1, Product A (500) is 2, Product B and D (300) tie next.

2. Step 2: Assign ranks with ties

   Since B and D tie at 300, they share rank 3.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   Product B rank = 3 [OK]

Hint: Ties share rank, next rank skips accordingly [OK]

Common Mistakes:

• Assigning different ranks to tied values
• Ignoring descending order
• Ranking Product B as 2 instead of 3

4.

Identify the error in this Tableau rank calculation:
RANK(SUM(Sales), 'ascending')

medium

A. RANK() does not accept a second argument.

B. SUM(Sales) cannot be used inside RANK().

C. The function should be RANK_DESC() instead.

D. The direction should be 'asc' not 'ascending'.

Solution

1. Step 1: Check valid direction keywords

   Tableau accepts 'asc' or 'desc' for direction, not 'ascending'.

2. Step 2: Confirm other parts are correct

   SUM(Sales) is valid, RANK() accepts second argument, and RANK_DESC() is not a function.

3. Final Answer:

   The direction should be 'asc' not 'ascending'. -> Option D

4. Quick Check:

   Use 'asc' or 'desc' for direction [OK]

Hint: Use 'asc' or 'desc' exactly for rank direction [OK]

Common Mistakes:

• Using full words like 'ascending' instead of 'asc'
• Thinking RANK() can't take direction
• Confusing RANK() with other functions

5.

You want to show the top 3 salespeople ranked by total sales, but if two salespeople tie for 3rd place, you want to show both. Which Tableau calculation should you use?

hard

A. Use RANK(SUM(Sales), 'desc') <= 3 to filter top 3 including ties.

B. Use INDEX() <= 3 to filter top 3 rows.

C. Use RANK(SUM(Sales), 'asc') <= 3 to filter top 3.

D. Use RANK_DENSE(SUM(Sales), 'desc') < 3 to filter top 3.

Solution

1. Step 1: Understand ranking with ties

   RANK() assigns same rank to ties, so filtering with <= 3 includes all tied at 3rd.

2. Step 2: Compare with other options

   INDEX() filters rows, not ranks; 'asc' ranks lowest sales; RANK_DENSE() does not skip ranks, so < 3 excludes ties at 3.

3. Final Answer:

   Use RANK(SUM(Sales), 'desc') <= 3 to filter top 3 including ties. -> Option A

4. Quick Check:

   RANK() <= 3 includes ties at 3rd [OK]

Hint: Filter with RANK() <= 3 to include ties at third place [OK]

Common Mistakes:

• Using INDEX() which ignores ranking
• Using ascending rank for top sales
• Using RANK_DENSE() < 3 excludes ties at 3