RANKX for ranking in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the RANKX function do in Power BI?

RANKX assigns a rank number to each item in a list based on a value, like sales amount, showing who is first, second, and so on.

intermediate

What is the basic syntax of RANKX?

RANKX(Table, Expression, [Value], [Order], [Ties]) where Table is the list to rank, Expression is the value to rank by, Value is optional, Order is ascending or descending, and Ties decides how to handle equal ranks.

intermediate

How does RANKX handle ties by default?

By default, RANKX gives the same rank to tied values and skips the next ranks accordingly.

advanced

Can RANKX rank items within a filtered group?

Yes, by using FILTER inside RANKX or applying filters to the table, you can rank items within specific groups like regions or categories.

beginner

Why is RANKX useful in dashboards?

It helps show top performers, like best-selling products or highest revenue customers, making data easy to understand and compare.

What does the RANKX function return?

A Sum of values

B A rank number for each item

C Average of values

D A filtered table

RANKX returns a rank number showing the position of each item based on the expression.

Which argument in RANKX defines the order of ranking?

A Order

B Expression

C Table

D Value

The Order argument sets if ranking is ascending or descending.

How does RANKX treat ties by default?

A Assigns the same rank and skips next ranks

B Assigns different ranks randomly

C Assigns the same rank and does not skip ranks

D Ignores ties

By default, RANKX assigns the same rank to tied values and skips the next ranks.

Can RANKX be used to rank within a specific group like a region?

A No, it ranks all data only

B Only with calculated columns

C Yes, by filtering the table inside RANKX

D Only if data is sorted

You can filter the table inside RANKX to rank within groups.

Which of these is a common use of RANKX in reports?

A Calculating averages

B Filtering data

C Creating charts

D Showing top sellers

RANKX is often used to show top sellers or top performers.

Explain how you would use RANKX to show the top 5 products by sales in a Power BI report.

Think about ranking all products by sales and then filtering to keep only the top 5.

Describe how RANKX handles ties and how you might change this behavior.

Consider what happens when two items have the same value and how to control ranking order.

Practice

1.

What does the RANKX function do in Power BI?

easy

A. It filters data based on a condition.

B. It calculates the sum of a column.

C. It assigns a rank number to each item based on a value.

D. It creates a new table from existing data.

Solution

1. Step 1: Understand the purpose of RANKX

   RANKX is used to order items by a value and assign a rank number.

2. Step 2: Compare with other functions

   Unlike filtering or summing, RANKX specifically ranks items.

3. Final Answer:

   It assigns a rank number to each item based on a value. -> Option C

4. Quick Check:

   RANKX = Assign rank [OK]

Hint: RANKX orders and numbers items by value quickly [OK]

Common Mistakes:

• Confusing RANKX with SUM or FILTER functions
• Thinking RANKX creates new tables
• Assuming RANKX filters data

2.

Which of the following is the correct syntax to rank sales by product using RANKX?

RANKX(____, [Sales])

easy

A. VALUES(Products[ProductName])

B. FILTER(Products, [Sales] > 0)

C. ALL(Products)

D. SUMMARIZE(Products, Products[ProductName])

Solution

1. Step 1: Identify the table argument for RANKX

   RANKX requires a table of items to rank, typically a list of unique products.

2. Step 2: Choose the correct function returning unique product names

   VALUES(Products[ProductName]) returns unique product names, suitable for ranking.

3. Final Answer:

   VALUES(Products[ProductName]) -> Option A

4. Quick Check:

   Use VALUES for unique items in RANKX [OK]

Hint: Use VALUES() to get unique items for RANKX table argument [OK]

Common Mistakes:

• Using ALL without specifying column
• Using FILTER which returns a table but may exclude items
• Using SUMMARIZE without proper columns

3.

Given the following measure:

Rank Sales = RANKX(ALL(Products), [Total Sales], , DESC, Dense)

What rank will the product with the highest sales receive?

medium

A. It depends on the number of products

B. 1

C. The highest sales product will have the lowest rank number

D. 0

Solution

1. Step 1: Understand RANKX parameters

   ALL(Products) removes filters, ranking all products by [Total Sales] descending.

2. Step 2: Determine rank for highest sales

   DESC order means highest value gets rank 1, Dense ranking handles ties without gaps.

3. Final Answer:

   1 -> Option B

4. Quick Check:

   Highest value rank = 1 [OK]

Hint: DESC order means highest value ranks 1 [OK]

Common Mistakes:

• Thinking rank starts at 0
• Confusing ascending and descending order
• Ignoring Dense ranking behavior

4.

Identify the error in this RANKX formula:

Rank = RANKX(Products, [Sales], , ASC)

medium

A. The third argument (value) is missing and required

B. The order ASC is invalid, must be DESC

C. RANKX cannot rank by [Sales]

D. Missing table argument should be ALL(Products)

Solution

1. Step 1: Check the table argument

   Using Products directly may apply current filters; ALL(Products) removes filters for full ranking.

2. Step 2: Confirm order and arguments

   ASC is valid; third argument is optional; ranking by [Sales] is allowed.

3. Final Answer:

   Missing table argument should be ALL(Products) -> Option D

4. Quick Check:

   Use ALL() to rank all items ignoring filters [OK]

Hint: Use ALL() to rank all items ignoring filters [OK]

Common Mistakes:

• Assuming ASC is invalid
• Thinking third argument is mandatory
• Not using ALL() to remove filters

5.

You want to rank products by sales but treat ties by assigning the same rank and skipping ranks after ties (standard competition ranking). Which RANKX parameter should you use?

hard

A. "Standard" to assign same rank and skip ranks after ties

B. "Dense" to assign same rank and no gaps after ties

C. "Skip" for ties to skip ranks after ties

D. "Unique" to assign unique ranks even if values tie

Solution

1. Step 1: Understand tie handling options in RANKX

   RANKX supports "Skip" (standard competition), "Dense" ranking methods.

2. Step 2: Identify standard competition ranking

   Standard competition ranking assigns same rank to ties and skips ranks after ties.

3. Final Answer:

   "Standard" to assign same rank and skip ranks after ties -> Option A

4. Quick Check:

   Standard = skip ranks after ties [OK]

Hint: Use "Standard" to skip ranks after ties in RANKX [OK]

Common Mistakes:

• Confusing Dense with Skip ranking
• Using non-existent "Unique" option
• Assuming ties always get unique ranks