topic Re: RANKX with TOPN struggle in DAX Commands and Tips

RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 03:00:53 GMT

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

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 $

Re: RANKX with TOPN struggle

Anonymous — Sun, 25 Aug 2019 05:05:11 GMT

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.

Re: RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 13:47:43 GMT

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

Re: RANKX with TOPN struggle

Anonymous — Sun, 25 Aug 2019 14:02:32 GMT

Hi,

Can you provide a sample of your data.

Thanks.

Re: RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 14:17:24 GMT

Is it enough?

Re: RANKX with TOPN struggle

Anonymous — Sun, 25 Aug 2019 14:20:06 GMT

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

Thanks.

Re: RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 14:32:27 GMT

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

Re: RANKX with TOPN struggle

Anonymous — Sun, 25 Aug 2019 14:52:17 GMT

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.

Re: RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 17:30:11 GMT

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

Re: RANKX with TOPN struggle

Anonymous — Sun, 25 Aug 2019 18:05:20 GMT

Try to remove the default summazisation on Salary.

Re: RANKX with TOPN struggle

NumeroENAP — Sun, 25 Aug 2019 18:54:26 GMT

I can't change it much.

Re: RANKX with TOPN struggle

NumeroENAP — Thu, 06 Feb 2020 05:48:44 GMT

Here's the link to my pbix file.

Re: RANKX with TOPN struggle

Anonymous — Mon, 26 Aug 2019 03:49:22 GMT

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 )

Re: RANKX with TOPN struggle

NumeroENAP — Mon, 26 Aug 2019 11:33:56 GMT

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