RANKX for ranking in Power BI - Cell-by-Cell Formula Trace

Sample Data

Sales data for different fruits with their sales numbers.

Cell	Value
A1	Product
B1	Sales
A2	Apples
B2	150
A3	Bananas
B3	200
A4	Cherries
B4	120
A5	Dates
B5	200
A6	Elderberries
B6	90

Formula Trace

RANKX(ALL('Table'[Product]), CALCULATE(SUM('Table'[Sales])), , DESC, Skip)

Step 1: ALL('Table'[Product])

Step 2: CALCULATE(SUM('Table'[Sales])) for each product

Step 3: RANKX over products by their sales values

Cell Reference Map

     A          B
1 Product    Sales
2 Apples     150  
3 Bananas    200  
4 Cherries   120  
5 Dates      200  
6 Elderberries 90 

Formula references all products in column A and sales in column B.

The formula uses all products from column A and their sales from column B to calculate ranks.

Result

     A          B       C
1 Product    Sales   Rank
2 Apples     150     3
3 Bananas    200     1
4 Cherries   120     4
5 Dates      200     1
6 Elderberries 90     5

The Rank column shows the rank of each product based on sales. Bananas and Dates share rank 1 because they have the highest sales.

Sheet Trace Quiz - 3 Questions

What does the ALL('Table'[Product]) part do in the formula?

A It filters only the top product.

B It removes filters so all products are ranked together.

C It sums the sales for each product.

D It sorts the products alphabetically.

Key Result

RANKX ranks items by a measure over all items ignoring filters, ties get same rank.