Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NumeroENAP
Helper III
Helper III

RANKX with TOPN struggle

Hi, 

 

I want to reproduce the Excel sheet of below, but I struggle a lot when it comes to repeat the rank everytime the team changes. I think I have to mix RANKX with TOPN, but I'm unable to.

 

Right now, I can only produce this code : 

Rank = RANKX(ALL(ALLplayers);ALLplayers[Salary])
 
Anyone can help me please?

 

 Player                 Team              Rank            Salary 

Player1Team11    8 384 000  $
Player2Team12    5 704 000  $
Player3Team13    5 618 000  $
Player4Team14    4 756 000  $
Player5Team15    4 384 000  $
Player6Team16    4 356 000  $
Player7Team17    4 160 000  $
Player8Team18    4 057 000  $
Player9Team21    8 384 000  $
Player10Team22    5 704 000  $
Player11Team23    5 618 000  $
Player12Team24    4 756 000  $
Player13Team25    4 384 000  $
Player14Team26    4 356 000  $
Player15Team27    4 160 000  $
Player16Team38    4 057 000  $
Player17Team31    8 384 000  $
Player18Team32    5 704 000  $
Player19Team33    5 618 000  $
Player20Team34    4 756 000  $
Player21Team35    4 384 000  $
Player22Team36    4 356 000  $
Player23Team37    4 160 000  $
Player24Team38    4 057 000  $
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi,

Create 2 measures as below and then drag them into the table.

Total Salary = SUM( Data[Salary] )
Rank = RANKX( ALLEXCEPT(Data,Data[Team]), [Total Salary], , DESC).
 
This is what i got on test data.
Result.PNG

View solution in original post

Anonymous
Not applicable

Hi,

I did the same thing and it works for me.

Total Salary = SUM( ALLplayers[Salary] )
Rank = RANKX( ALLEXCEPT( ALLplayers, ALLplayers[Team] ),[Total Salary],,DESC )
 
result.PNG

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

Hi,

Create 2 measures as below and then drag them into the table.

Total Salary = SUM( Data[Salary] )
Rank = RANKX( ALLEXCEPT(Data,Data[Team]), [Total Salary], , DESC).
 
This is what i got on test data.
Result.PNG

It didn't worked as espected. It's still ranking all players from 1 to 1000, despite the team column. 

 

Sans titre.png

Anonymous
Not applicable

Hi,

Can you provide a sample of your data.

Thanks.

Is it enough?

 

Sans titre.png

Anonymous
Not applicable

Could you please just copy paste the data (not the image), so that am able to use it as it is.

Thanks.

NameSalaryTeam
Claude Giroux10688000Hurricanes
Sidney Crosby10118000Islanders
Alex Ovechkin10104000Hurricanes
Shea Weber9257000Lightning
Blake Wheeler9065100Islanders
Evgeni Malkin8885000Blackhawks
Phil Kessel8679000Maple Leafs
Anze Kopitar8384000Avalanche
Connor McDavid8144000Blues
Patrice Bergeron7958000Capitals
Nicklas Backstrom7894000Senators
Mattias Ekholm7518000Blues
Taylor Hall7103000Bruins
Joe Pavelski6909000Predators
Dustin Byfuglien6871000Canadiens
Matt Niskanen6763000Oilers
Gabriel Landeskog6652000Sharks
David Krejci6624000Canadiens
Eric Staal6579000Capitals
Reilly Smith6392000Kings
Derek Stepan6174000Lightning
Kevin Shattenkirk6156000Blackhawks
Steven Stamkos6027000Devils
Alexander Radulov5996000Wild
Adam Henrique5913000Capitals
Roman Josi5850000Ducks
T.J. Oshie5848000Oilers
Jakob Silfverberg5793000Ducks
Patric Hornqvist5785000Lightning
Alex Killorn5764000Oilers
Brad Marchand5704000Avalanche
Zdeno Chara5669000Golden Knights
Mark Giordano5669000Wild
Erik Karlsson5641000Devils
Zack Smith5640000Panthers
Anonymous
Not applicable

Hi,

It works for me. Make sure you drag the 'Total Salary' measure in the table and not the predefined column. The same measure is used in the RANKX function.

 

Total Salary = SUM( Data[Salary] )
Rank = RANKX( ALLEXCEPT( Data, Data[Team] ),[Total Salary], ,DESC )
 
result.PNG
Thanks.

I'm not sure what I'm doing wrong...

 

Sans titre.png

Anonymous
Not applicable

Try to remove the default summazisation on Salary.

Here's the link to my pbix file.

Anonymous
Not applicable

Hi,

I did the same thing and it works for me.

Total Salary = SUM( ALLplayers[Salary] )
Rank = RANKX( ALLEXCEPT( ALLplayers, ALLplayers[Team] ),[Total Salary],,DESC )
 
result.PNG

That is weird... I really don't know what's the problem... (sigh)

I can't change it much.

 

Sans titre.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors