RANKX in Power BI DAX — Rank Cities and Products
RANKX returns the rank of the current item among a table of competitors evaluated by an expression — usually a measure like [Total Sales] — and filter context decides who is on the leaderboard.
Chala mitrano! Leaderboards make sense to every stakeholder. Why? “Top city” is clearer than a raw table of twenty numbers. How? We write a beginner RANKX on FreshBasket (fictional) cities, talk ties, and peek at ALL vs ALLSELECTED. Competitive, but calm.
Quick answer
Vertical ranking:
- Solid
[Total Sales]first. City Rank = RANKX ( ALL ( Customer[City] ), [Total Sales], , DESC ).- Table visual: City + Total Sales + City Rank.
- Rank 1 should be the highest sales city.
- Decide how ties should look (skip vs dense) when you need precision.
- Change ALL vs ALLSELECTED intentionally — do not guess.
RANKX ( ALL ( Customer[City] ), [Total Sales], , DESC )
Why RANKX, rows in / number out
| City | Total Sales |
|---|---|
| Mumbai | 140 |
| Nashik | 80 |
| Pune | 60 |
- Why RANKX: managers want position, not only rupees.
- Competitors table = all cities (via
ALL ( Customer[City] )). - Expression =
[Total Sales]. - DESC → biggest first. Mumbai 140 → rank 1. Nashik 80 → 2. Pune 60 → 3.
- Number out is a rank integer, not the sales amount.
- If two cities both had 140, ties share logic — say the rule aloud before arguing.
RANKX ranks the current item among a table of competitors using an expression (often a measure).
मित्रांनो — RANKX ranks the current item among a table of competitors using an expression (often a measure).
मित्रों — RANKX ranks the current item among a table of competitors using an expression (often a measure).
What do I need before this guide?
- ALL family.
- Course: RANKX and TOPN.
Before and after (look at the tables first)
Before
आधी (Before)
पहले (Before)
After
नंतर (After)
बाद में (After)
RANKX shape
City Rank =
RANKX (
ALL ( Customer[City] ),
[Total Sales],
,
DESC
)
- Table argument = who competes (all cities here).
- Expression = what we rank by.
- DESC → biggest sales gets rank 1.
- Adjust table/column names to your model.
Shop analogy: RANKX is the race result sheet. Sales is the finish time (higher is better with DESC). ALL is “everyone races,” not only the one city on the row.
Ties
Ties matter: skip vs dense ranking changes how equal values share places — say which you want.
मित्रांनो — Ties matter: skip vs dense ranking changes how equal values share places — say which you want.
मित्रों — Ties matter: skip vs dense ranking changes how equal values share places — say which you want.
- Equal sales can share ranks.
- Some patterns skip numbers after ties; dense ranking does not.
- Say the rule aloud before debating “wrong” ranks.
Filter context and scope
Who you rank against depends on filter context — ALL / ALLSELECTED / visible rows change the leaderboard.
मित्रांनो — Who you rank against depends on filter context — ALL / ALLSELECTED / visible rows change the leaderboard.
मित्रों — Who you rank against depends on filter context — ALL / ALLSELECTED / visible rows change the leaderboard.
ALL ( City )makes every city compete even if the row is filtered.ALLSELECTEDvariants rank inside what the user currently selected.- Wrong scope = “why is everything rank 1?”
Mistakes and calm fixes
| Symptom | Likely cause | Fix |
|---|---|---|
| All rank 1 | Competitors table too small | Use ALL / wider table |
| Rank ignores slicer wrongly | ALL vs ALLSELECTED mismatch | Match business question |
| Blank ranks | Expression blank | Fix base measure |
| Card shows one number only | No competitor context | Use table/matrix by City |
Ghabru naka 😅 — RANKX without a competitor table is like a race with one runner.
Ravindra Bagale's Tip
Interview line that scores: “RANKX needs a table of competitors and an expression; ALL controls whether ranks ignore the City filter on the row.” Then give one example. Got it?
Ravindra Bagale's Tip – मराठी
Interview line जी scores करते: “RANKX ला competitors ची table आणि expression लागते; ALL ठरवते की ranks row वरील City filter ignore करतात की नाही.” मग एक उदाहरण द्या. समजलं का?
Ravindra Bagale's Tip – हिंदी
Interview line जो score करती है: “RANKX को competitors की table और expression चाहिए; ALL तय करता है कि ranks row के City filter को ignore करें या नहीं.” फिर एक उदाहरण दो. समझ में आया?
Practice task
- Build City Rank with ALL ( City ).
- Sort the table by rank (Mumbai should be 1 when it has 140).
- Add a Region slicer; try an ALLSELECTED variant.
- Find a tie (or create equal sample sales) and note ranks.
- Write which scope your manager wants.
Got it? RANKX ranks among a table; scope is everything. Next: percent of total with ALLSELECTED. Let's continue.
समजलं का? RANKX ranks among a table; scope is everything. Next: percent of total with ALLSELECTED. Aata pudhe jaauya.
समझ में आया? RANKX ranks among a table; scope is everything. Next: percent of total with ALLSELECTED. आगे बढ़ते हैं.
Frequently asked questions
What does RANKX do?
It returns the rank of the current row’s expression among the values computed for each row of a table.
Why ALL ( City )?
So every city competes in the ranking even when the visual filters to one city — classic pattern; variants exist.
ASC or DESC?
DESC for “highest sales = rank 1”; ASC for lowest-first rankings.
Ties?
Equal values can share ranks; learn dense vs skip when you need exact leaderboard rules.
RANKX vs TOPN?
RANKX assigns a number; TOPN returns a table of top items — different tools.
Course lesson?
Ranking with RANKX and TOPN in the DAX chapter.