Question

How can I use RANKX() in DAX to rank customers within each region while keeping ties properly handled?
I need a Power BI measure that ranks customers within each region using RANKX, ensuring that ties are properly handled without skipping ranks. What is the best way to structure the DAX formula to achieve correct rankings while considering filter contexts?

Answer 1

The RANKX() function allows you to rank customers in each region while taking care of tied ranks. Therefore, make sure ranking is calculated over a context filtered by area, not skipping ranks due to tie situations.

DAX Measure to Rank Customers in Each Region

Customer Rank = 
RANKX(
    CALCULATETABLE( 
        VALUES( 'Customers'[CustomerName] ), 
        ALLSELECTED( 'Customers' ) 
    ),
    CALCULATE( SUM( 'Sales'[TotalSales] ) ),
    , 
    DESC, 
    DENSE
)

Explanation:

CALCULATETABLE(VALUES('Customers'[CustomerName]), ALLSELECTED('Customers'))

Creates a virtual table containing unique customers within the selected region so that ranking is done per region.

CALCULATE(SUM('Sales'[TotalSales]))

Defines the ranking criteria (total sales per customer).

RANKX(..., ..., DESC, DENSE)

DESC → Ranks in descending order (highest sales first).

DENSE → Tied values receive the same rank, while the next rank is given without skipping.

Handling Filter Context

The ranking will respect all active filters applied in the ALLSELECTED function, for instance, a particular region selected in a slicer.  

If you want to ignore all slicers that affect regions in the rankings, replace ALLSELECTED('Customers') with ALL('Customers').

This will ensure that customers are ranked properly within each region without skipping the sequence for any ties.