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 if you could help me with the DAX formula to have the column rank 2016 & rank 2017 (which is for me a rankx based on 3 criteria: country, client and product name according to either 2016 sales or 2017 sales) ?

CountryClientProduct name2016 sales2017 salesRank 2016Rank 2017Final Rank 2016Final Rank 2017
ItalyITA_1A1005012Top 2Top 2
ItalyITA_1B5010021Top 2Top 2
ItalyITA_1C252533OthersOthers
ItalyITA_2A2005014Top 2Others
ItalyITA_2B15020022Top 2Top 2
ItalyITA_2C7515033OthersOthers
ItalyITA_2D5030041OthersTop 2
GermanyGER_1A305011Top 2Top 2
GermanyGER_1B204022Top 2Top 2
GermanyGER_1C101033OthersOthers
GermanyGER_2A40030012Top 2Top 2
GermanyGER_2B35020023Top 2Others
GermanyGER_2C10050031OthersTop 2

 

Thanks in advance,

 

Alex

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

2 Replies

  • 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