Forum Discussion

Alex_192's avatar
Alex_192
Frequent Visitor
9 years ago
Solved

Rank with 3 conditions

Hello Everyone,   I would like to look at my top 2 product per client per country. To do that I have created a new table (see example below) with the information I need.   I would like to know i...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Alex_192,

     

    Based on my test, you should be able to use the formulas below to create new calculate column in this scenario. :smileyhappy:

     

    Rank 2016 =
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2016 sales]
)

    Rank 2017 = 
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2017 sales]
)

    Note: Just replace 'Table1' with your real table name.

     

     

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Alex_192,

 

Based on my test, you should be able to use the formulas below to create new calculate column in this scenario. :smileyhappy:

 

Rank 2016 =
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2016 sales]
)

Rank 2017 = 
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2017 sales]
)

Note: Just replace 'Table1' with your real table name.

 

 

 

Regards

Alex_192's avatar
Alex_192
Frequent Visitor
9 years ago

Hi v-ljerr-msft,

 

Thank you for your help !!

 

It's working :)

 

Best,

 

Alex