Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
burnaNayak
Regular Visitor

RANKX Function

‎11-15-2020 03:42 PM

Hi ,

 

I Have applied Ranx to my Table which looks as below . In the first snapshot below have grouped the countries to show the Sales amount in Desc Order .

CountryRank = RANKX(ALLSELECTED(user_first_deposit[country]),CALCULATE(SUM(user_first_deposit[usd_amount]))) . Now i Want another column "Amount TOP4 + Others " , where it shows top 4 countries and anything that exceeds 4 should be listed under others , the second snapshot is an example of how it should look like . Can anybody help me in this .
1ST SNAPSHOT

burnaNayak_0-1605483220532.png

2 ND SNAPSHOT

countryCountryRankusd_amountAmount TOP4 + Others
China1128393088China
Australia233131855Australia
Taiwan34507356Taiwan
Hong Kong SAR China42191428Hong Kong SAR China
New Zealand52037118Others 
Singapore61383320Others 
Malaysia71280955Others 
Philippines81128673Others 
Canada9910346Others 
United Kingdom10687390Others 
Cambodia11611389Others 
Switzerland12411285Others 
United Arab Emirates13342534Others 
Netherlands14330287Others 
Turkey15286855Others 
South Korea16256078Others 
Italy17243511Others 
Macau SAR China18225516Others 
Germany19220078Others 
Vietnam20159515Others 
Indonesia21140793Others 
Thailand22110061Others 
Norway2392062Others 
France2489876Others 
Spain2585145Others 
Peru2676861Others 
Portugal2765064Others 
United States2860863Others 
Belgium2949253Others 
Bhutan3049021Others 
Qatar3148396Others 
Japan3247374Others 
India3347196Others 
Morocco3443827Others 
Pakistan3538784Others 
Saudi Arabia3629448Others 
Kuwait3729294Others 
Luxembourg3828678Others 
Zambia3928132Others 
Brunei4027662Others 
Mexico4124950Others 
Ireland4223599Others 
Timor-Leste4320845Others 
Hungary4420674Others 
Finland4520314Others 
Sweden4619178Others 
Czech Republic4718720Others 
Romania4818278Others 
Slovakia4916703Others 
Denmark5014367Others 
South Africa5114327Others 
Cayman Islands5213500Others 
Russia5313233Others 
Hong Kong5413230Others 
U.S. Minor Outlying Islands5512900Others 
Nigeria5612170Others 
Austria5711459Others 
Greece589917Others 
Bangladesh598201Others 
Kenya608008Others 
Brazil616936Others 
SEf626430Others 
Colombia635330Others 
Poland645143Others 
Lebanon654673Others 
Cyprus664583Others 
American Samoa674567Others 
U.S. Virgin Islands684295Others 
Maldives694195Others 
Azerbaijan704110Others 
Myanmar [Burma]713957Others 
Mauritius723553Others 
Iceland733380Others 
Bulgaria743090Others 
Oman752900Others 
Bahrain762300Others 
Israel772118Others 
Monaco782100Others 
Belarus791771Others 
Jordan801600Others 
Guam811500Others 
Guatemala821460Others 
Afghanistan831372Others 
Sri Lanka841347Others 
Lithuania851345Others 
Martinique861034Others 
Argentina871000Others 
Botswana871000Others 
Panama871000Others 
Tajikistan90994Others 
Benin91899Others 
Jamaica92719Others 
Uganda93715Others 
Egypt94696Others 
Saint Martin95602Others 
Slovenia96600Others 
Uzbekistan97570Others 
Malta98500Others 
Croatia99411Others 
Ukraine100404Others 
Ecuador101300Others 
Tanzania101300Others 
Moldova103250Others 
Georgia104200Others 
Paraguay104200Others 
Chile106191Others 
Estonia107170Others 
Cuba108168Others 
Mozambique109140Others 
Ghana110120Others 
Latvia111110Others 
Equatorial Guinea112104Others 
Costa Rica113100Others 
Dominican Republic113100Others 
Nepal113100Others 
Northern Mariana Islands113100Others 
Serbia113100Others 
Suriname113100Others 
Unknown or Invalid Region113100Others 
EN12099Others 

 

Labels:
Message 1 of 5
827 Views
0
1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion
In response to burnaNayak

‎11-16-2020 02:26 AM

Hi, @burnaNayak , strange enough😂 it's works at my side. I attached a file for your further reference.

Screenshot 2020-11-16 112108.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

Message 4 of 5
755 Views
0
4 REPLIES 4
Fowmy
Super User
Super User

‎11-15-2020 10:48 PM

@burnaNayak 

Can you check this video from SQLBI, it can be useful for your to get the results you are after.


https://www.youtube.com/watch?v=tgL7D3JTa_E

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Message 5 of 5
773 Views
0
CNENFRNL
Community Champion
Community Champion

‎11-15-2020 05:09 PM

Hi, @burnaNayak , you're only one inch away from your goal.

But there's a glitch in your measure, which throws error in such a viz

Screenshot 2020-11-16 020350.png

 

Therefore, I amend it and author another measure to achieve the goal as you described.

CountryRank amended = 
RANKX (
    ALLSELECTED ( user_first_deposit ),
    CALCULATE ( SUM ( user_first_deposit[usd_amount] ) )
)

////////////////////
TOP4 = IF( [CountryRank amended] <= 4, MAX(user_first_deposit[country]), "Other")

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Message 2 of 5
798 Views
0
burnaNayak
Regular Visitor
In response to CNENFRNL

‎11-15-2020 05:40 PM

Hi , 

If I add the countryrankamended , it looks like the 1st snapshot 

But once i add the Top 4 column , the 2nd snapshot changes as below and is not giving me what i expected .

1st Snapshot 

burnaNayak_14-1605490699085.png

 

 

2nd Snapshot 

 

 

burnaNayak_12-1605490609695.png

 

 

Message 3 of 5
788 Views
0
CNENFRNL
Community Champion
Community Champion
In response to burnaNayak

‎11-16-2020 02:26 AM

Hi, @burnaNayak , strange enough😂 it's works at my side. I attached a file for your further reference.

Screenshot 2020-11-16 112108.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Message 4 of 5
756 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All