# Thread: Fighting with RANKX function Thanks: 0 Likes: 0

1. ## Fighting with RANKX function

Hello Guys!!

I´ve been fighting for all day long with the function RANKX and I can´t be able to find a solution:

The function is simple but it doesn´t work as I expected:

I want to do a ranking of Sales by ID, but (here it´s where i found the problem) the Pivot table have to accept other references (rows) without modify the Rankx function, such as Client name and Country,...

If I only select into the table the ID the result is correct:

 Product 1 Product 2 ID NetSales Rank NetSales Rank 60322996 98.216 € 30 1.476.888 € 1 60081106 426 513.356 € 2 60301834 408.914 € 1 77.795 € 62 60304308 7.343 € 226 417.174 € 3 60308978 3.569 € 272 393.316 € 4

However when I introduce another Row field the Rankx stops working properly, and I only get the correct result if i look at the ID Total:

 Product 1 Product 2 ID Client Country NetSales Rank NetSales Rank 60322996 Client 1 Spain 98.216 € 1 1.476.888 € 1 60322996 Total 98.216 € 30 1.476.888 € 1 60081106 Client 2 England 1 513.356 € 1 60081106 Total 426 513.356 € 2 60301834 Client 3 USA 408.914 € 1 77.795 € 1 60301834 Total 408.914 € 1 77.795 € 62 60304308 Client 4 China 7.343 € 1 417.174 € 1 60304308 Total 7.343 € 226 417.174 € 3 60308978 Client 5 Spain 3.569 € 1 393.316 € 1 60308978 Total 3.569 € 272 393.316 € 4

The function:

Rank := RANKX ( ALL(Table[ID]) , SUM(Table[Net_Sales]) )

So Would it be possible to get the value from the Id subtotal in the row?

Thanks!!!

2. ## Re: Fighting with RANKX function

This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

3. ## Re: Fighting with RANKX function

Originally Posted by Matt Allington
This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))
Thank you Matt, it almost work but not completely. I mean, it works great with totals however when I introduce the Product Category in the Columns, the result is not correct.

I attached the file to make it easier.

https://www.dropbox.com/s/68dlglqf60...Rank.xlsx?dl=0

Thank you

4. ## Re: Fighting with RANKX function

Originally Posted by Ppivot
Thank you Matt, it almost work but not completely. I mean, it works great with totals however when I introduce the Product Category in the Columns, the result is not correct.

I attached the file to make it easier.

https://www.dropbox.com/s/68dlglqf60...Rank.xlsx?dl=0

Thank you
Nor with the Year, it´s seems that doesn't be affected by the filters (Year and Category)

5. ## Re: Fighting with RANKX function

Originally Posted by Matt Allington
This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))
Originally Posted by Matt Allington
This is a guess - I really need to test it but I am on my phone. Try wrapping the current measure inside this.

=calculate( your current formula here, ALLexcept(table,table[id]))

I think I have it, thank you a lot for your Tip!!!!

The formula: (If it can help to others)

Calculate(

RANKX( ALL(Table[ID] , SUM(Table[Net Sales] ) ,
ALLEXCEPT( Table, Table[ID], Calendar, Category )
)

In the ALLEXCEPT you have to put all the filters that you wanna still be affected in the Pivot Table, that's why I've put it the Calendar and Category Tables.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•