cancel
Showing results for
Did you mean:
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])

Player                 Team              Rank            Salary

 Player1 Team1 1 8 384 000  \$ Player2 Team1 2 5 704 000  \$ Player3 Team1 3 5 618 000  \$ Player4 Team1 4 4 756 000  \$ Player5 Team1 5 4 384 000  \$ Player6 Team1 6 4 356 000  \$ Player7 Team1 7 4 160 000  \$ Player8 Team1 8 4 057 000  \$ Player9 Team2 1 8 384 000  \$ Player10 Team2 2 5 704 000  \$ Player11 Team2 3 5 618 000  \$ Player12 Team2 4 4 756 000  \$ Player13 Team2 5 4 384 000  \$ Player14 Team2 6 4 356 000  \$ Player15 Team2 7 4 160 000  \$ Player16 Team3 8 4 057 000  \$ Player17 Team3 1 8 384 000  \$ Player18 Team3 2 5 704 000  \$ Player19 Team3 3 5 618 000  \$ Player20 Team3 4 4 756 000  \$ Player21 Team3 5 4 384 000  \$ Player22 Team3 6 4 356 000  \$ Player23 Team3 7 4 160 000  \$ Player24 Team3 8 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.
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 )

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.
Helper III

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

Anonymous
Not applicable

Hi,

Can you provide a sample of your data.

Thanks.

Helper III

Is it enough?

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.

Helper III
 Name Salary Team Claude Giroux 10688000 Hurricanes Sidney Crosby 10118000 Islanders Alex Ovechkin 10104000 Hurricanes Shea Weber 9257000 Lightning Blake Wheeler 9065100 Islanders Evgeni Malkin 8885000 Blackhawks Phil Kessel 8679000 Maple Leafs Anze Kopitar 8384000 Avalanche Connor McDavid 8144000 Blues Patrice Bergeron 7958000 Capitals Nicklas Backstrom 7894000 Senators Mattias Ekholm 7518000 Blues Taylor Hall 7103000 Bruins Joe Pavelski 6909000 Predators Dustin Byfuglien 6871000 Canadiens Matt Niskanen 6763000 Oilers Gabriel Landeskog 6652000 Sharks David Krejci 6624000 Canadiens Eric Staal 6579000 Capitals Reilly Smith 6392000 Kings Derek Stepan 6174000 Lightning Kevin Shattenkirk 6156000 Blackhawks Steven Stamkos 6027000 Devils Alexander Radulov 5996000 Wild Adam Henrique 5913000 Capitals Roman Josi 5850000 Ducks T.J. Oshie 5848000 Oilers Jakob Silfverberg 5793000 Ducks Patric Hornqvist 5785000 Lightning Alex Killorn 5764000 Oilers Brad Marchand 5704000 Avalanche Zdeno Chara 5669000 Golden Knights Mark Giordano 5669000 Wild Erik Karlsson 5641000 Devils Zack Smith 5640000 Panthers
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 )

Thanks.
Helper III

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

Anonymous
Not applicable
Try to remove the default summazisation on Salary.
Helper III

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 )

Helper III

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

Helper III

I can't change it much.

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors