Ranking and Top N

01The problem

The sales director wants a customer league table with each customer's rank this year and last year, so the account team can see who's climbing and who's slipping. She also wants one number for the board: "How dependent are we on our biggest five customers?"

Sorting a table visual puts customers in order, but it doesn't give you a rank you can show, compare or filter on, and it can't tell you what the top five add up to. Both need DAX.

02The concept

RANKX

RANKX ( <table>, <expression>, [value], [order], [ties] )

RANKX evaluates the expression for every row of the table, then finds where the current value sits among them. For a customer rank:

Customer Rank = RANKX ( ALL ( customers[customer_name] ), [Revenue] )

The table argument is the key decision. ALL ( customers[customer_name] ) ranks against every customer, ignoring the visual's customer filter, which is what you want: on the row for Chuks Trading Co., the rank is calculated against all 90 customers, not against just Chuks. Use ALLSELECTED instead if the rank should be among the customers the user has selected.

Other arguments:

• order: DESC (the default, highest = 1) or ASC.
• ties: SKIP (the default: 1, 2, 2, 4) or DENSE (1, 2, 2, 3).

Two things to tidy

• The total row. At the total there's no single customer, so the rank is meaningless (it shows 1). Return BLANK there with ISINSCOPE ( customers[customer_name] ), which is true only when the visual is grouped by customer.
• Customers with no sales in the period get ranked last. Return BLANK when [Revenue] is blank.

TOPN

TOPN ( n, <table>, <expression> ) returns the top n rows of a table as a virtual table, which you can then iterate:

Top 5 Revenue =
SUMX ( TOPN ( 5, ALL ( customers[customer_name] ), [Revenue] ), [Revenue] )

Let the user choose N

Modeling → New parameter → Numeric range creates a slicer and a measure, such as [Top N Value], that returns the selected number. Use it in place of 5 to make the analysis interactive.

03Example

Customer Rank =
IF (
    ISINSCOPE ( customers[customer_name] ) && NOT ISBLANK ( [Revenue] ),
    RANKX ( ALL ( customers[customer_name] ), [Revenue] )
)

Customer Rank LY =
VAR RevenueLY = CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF (
        ISINSCOPE ( customers[customer_name] ) && NOT ISBLANK ( RevenueLY ),
        RANKX (
            ALL ( customers[customer_name] ),
            CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ),
            RevenueLY
        )
    )

Top 5 Share =
VAR Top5 = TOPN ( 5, ALL ( customers[customer_name] ), [Revenue] )
RETURN
    DIVIDE ( SUMX ( Top5, [Revenue] ), CALCULATE ( [Revenue], ALL ( customers[customer_name] ) ) )

In Customer Rank LY, the third argument of RANKX gives the value to place in the ranking (this customer's revenue last year), and the second argument ranks every customer by their own revenue last year.

With a 2026 filter, the top of the league table looks like this:

04Walkthrough

1. Add Customer Rank and put it in a table with customers[customer_name] and [Revenue]. Check that the total row is blank.
2. Remove the ISINSCOPE test and look at the total row: it shows 1. Put the test back.
3. Add Customer Rank LY, filter the page to 2026, and sort by Customer Rank. Who has climbed the most?
4. Add Top 5 Share to a card, with a Date[Year] slicer.
5. Create a numeric range parameter called Top N (1 to 20, increment 1). Change Top 5 Share to use [Top N Value] instead of 5, rename it Top N Share, and try different values.

05Practice

06More practice

07Check your understanding