13. DAX: Data Analysis Expressions
13.17 RANKX and TOPN in Power BI DAX with Example
Quick answer: the short, self-contained version of this lesson is the guide DAX RANKX.
RANKX in Power BI with example — in 30 seconds
What: RANKX gives a rank number to each item, for example each city by sales.
Why: "Top 3 cities this month" is the first slide in many monthly reviews.
Tiny example: Mumbai 1, Pune 2, Nashik and Nagpur tie at 3.
RANKX in Power BI with example — 30 सेकंदात
काय: RANKX प्रत्येक item ला rank number देतो, उदा. sales नुसार प्रत्येक शहराला.
का: "या महिन्याची Top 3 शहरं" ही बऱ्याच monthly reviews ची पहिली slide असते.
छोटं उदाहरण: Mumbai 1, Pune 2, Nashik आणि Nagpur 3 वर बरोबरीत.
RANKX in Power BI with example — 30 सेकंड में
क्या: RANKX हर item को rank number देता है, जैसे sales के हिसाब से हर शहर को.
क्यों: "इस महीने के Top 3 शहर" कई monthly reviews की पहली slide होती है.
छोटा उदाहरण: Mumbai 1, Pune 2, Nashik और Nagpur 3 पर बराबर.
First look at the data (before → after):
Same before / after tables as text
Before — Total Sales by City
| City | Total Sales |
|---|---|
| Pune | ₹1,200 |
| Mumbai | ₹1,500 |
| Nashik | ₹300 |
| Nagpur | ₹300 |
About this data: This table uses columns: City, Total Sales. This table uses columns: City, Total Sales, Rank (Skip), Rank (Dense). Values shown are the lesson examples already on this page (not a new dataset).
Formula / click path
City Rank = RANKX(ALL(DarkStore[City]), [Total Sales], , DESC, Skip)
City Rank Dense = RANKX(ALL(DarkStore[City]), [Total Sales], , DESC, Dense)
After — Ranks
| City | Total Sales | Rank (Skip) | Rank (Dense) |
|---|---|---|---|
| Mumbai | ₹1,500 | 1 | 1 |
| Pune | ₹1,200 | 2 | 2 |
| Nashik | ₹300 | 3 | 3 |
| Nagpur | ₹300 | 3 | 3 |
Do it step by step:
- New measure: City Rank (formula above).
- Use ALL(DarkStore[City]) so every row ranks against all cities.
- Put City, Total Sales and City Rank in a table. Sort by rank.
- If a 5th city had less sales, Skip would give it 5 and Dense would give it 4.
एक-एक step करून बघा:
- New measure: City Rank (वरचा formula).
- ALL(DarkStore[City]) वापरा, म्हणजे प्रत्येक row सगळ्या शहरांशी तुलना करून rank होते.
- City, Total Sales आणि City Rank एका table मध्ये टाका. Rank ने sort करा.
- 5 व्या शहराचे sales कमी असते तर Skip त्याला 5 देईल आणि Dense 4.
एक-एक step करके देखिए:
- New measure: City Rank (ऊपर वाला formula).
- ALL(DarkStore[City]) इस्तेमाल कीजिए, ताकि हर row सारे शहरों से तुलना करके rank हो.
- City, Total Sales और City Rank एक table में डालिए. Rank से sort कीजिए.
- 5वें शहर की sales कम होती तो Skip उसे 5 देगा और Dense 4.
RANKX
RANKX(<table>, <expression>, [value], [order], [ties])
Product Rank =
IF(
HASONEVALUE(Product[Product Name]),
RANKX(ALL(Product[Product Name]), [Total Sales], , DESC, DENSE)
)
-- Rank dark stores by speed: fastest (lowest time) = rank 1
Store Speed Rank =
IF(
HASONEVALUE(DarkStore[Store Name]),
RANKX(ALL(DarkStore[Store Name]), [Avg Delivery Time (mins)], , ASC, DENSE)
)
Customer Rank in City =
RANKX(ALLSELECTED(Customer[Customer Name]), [Total Sales], , DESC)
- Use
ALL(...)so every item is ranked against all items, not only itself. HASONEVALUEhides the meaningless rank on the total row.- Order:
DESC(highest = 1) orASC(lowest = 1, useful for delivery time). - Ties:
SKIP(default: 1, 2, 2, 4) orDENSE(1, 2, 2, 3).
TOPN
TOPN(<n>, <table>, <orderBy expression>, [order]) returns a table with the top N rows.
Top 5 Products Sales =
CALCULATE(
[Total Sales],
TOPN(5, ALL(Product[Product Name]), [Total Sales], DESC)
)
Top 5 Share % = DIVIDE([Top 5 Products Sales], CALCULATE([Total Sales], ALL(Product)))
-- Calculated table of the 10 busiest dark stores
Top 10 Stores =
TOPN(
10,
ADDCOLUMNS(VALUES(DarkStore[Store Name]), "Orders", [Total Orders]),
[Orders], DESC
)
Top N without DAX
For a simple "show fakt the top 10 products in this chart", use the Top N filter in the Filters pane (Module 22). Use DAX TOPN when you need a number (like the share of the top 5) or a table.
Ravindra Bagale's Tip
Friends, many students use RANKX(Product, …) and get rank 1 for every row, because the table argument still has the current filter. Use ALL(Product[Product Name]) or ALLSELECTED as the table, and remember that RANKX ties are Skip by default, so pass Dense if you want 1, 2, 2, 3. Never forget this.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students RANKX(Product, …) वापरतात आणि प्रत्येक row ला rank 1 मिळतो, कारण table argument वर अजून current filter असतो. Table म्हणून ALL(Product[Product Name]) किंवा ALLSELECTED वापरा, आणि लक्षात ठेवा की RANKX मध्ये ties default ने Skip असतात, म्हणून 1, 2, 2, 3 हवं असेल तर Dense द्या. हे अजिबात विसरू नका.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students RANKX(Product, …) इस्तेमाल करते हैं और हर row को rank 1 मिलता है, क्योंकि table argument पर अभी भी current filter होता है. Table के रूप में ALL(Product[Product Name]) या ALLSELECTED इस्तेमाल करो, और याद रखो कि RANKX में ties default से Skip होते हैं, इसलिए 1, 2, 2, 3 चाहिए तो Dense दो. यह बिल्कुल मत भूलना.
Loading DAX practice tasks...
- Shop rank (Easy): Shown per ShopName. Measure: Shop Rank = rank of [Revenue] among all shop names (1 = highest).
- Barista rank (Easy): Shown per Barista. Measure: Barista Rank = rank of [Revenue] among all baristas.
- Smallest city first (Easy): Shown per City. Measure: Low Rank = rank of [Revenue] with the lowest as 1.
- Dense rank of items (Medium): Shown per Item. Measure: Item Rank = dense rank of [Revenue] among all items, highest = 1.
- Top 2 shops revenue (Medium): Measure: Top 2 Revenue = [Revenue] added over the 2 shops with the highest [Revenue].
- Top 3 baristas revenue (Medium): Measure: Top 3 Baristas = [Revenue] added over the 3 baristas with the highest [Revenue].
- Best shop name (Medium): Measure: Best Shop = the ShopName with the highest [Revenue].
- Most cups of one item (Hard): Measure: Max Item Cups = the highest total Cups of any single Item.
- Top 2 label (Hard): Shown per ShopName. Measure: Tier = "Top 2" if the shop rank by [Revenue] is 2 or better, else "Other".
- Best item per country (Hard): Shown per Country. Measure: Best Item Revenue = the highest [Revenue] of any Item.
Revision card