Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Fighting with RANKX function

    Hello Guys!!

    Ive been fighting for all day long with the function RANKX and I cant be able to find a solution:

    The function is simple but it doesnt work as I expected:

    I want to do a ranking of Sales by ID, but (here its 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. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Feb 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fighting with RANKX function

    Quote Originally Posted by Matt Allington View Post
    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. #4
    New Member
    Join Date
    Feb 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fighting with RANKX function

    Quote Originally Posted by Ppivot View Post
    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, its seems that doesn't be affected by the filters (Year and Category)

  5. #5
    New Member
    Join Date
    Feb 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Fighting with RANKX function

    Quote Originally Posted by Matt Allington View Post
    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]))
    Quote Originally Posted by Matt Allington View Post
    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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