Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculated column RANKX with multiple columns

Hi All,

 

As per the image below, I would like to create a calculated column (Rankx) to rank the top products at customer level. By doing so, I would then be able to create a calculated column ("Top N + Other") to show the top 3 product name and the rest to be grouped as "Other".

 

Does anyone know how to apply the RANKX formula to create a calculated column to rank products at customer level? I tried applying the RANKX formula but it seems to only rank by product level on overall basis instead of at customer level.

 

Thank you!

 

Need Assistance
Need Help
Need Help DAX
Need Help DAX measure
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

    The problem is because of that change you did in the code, remove that SUM from line 4 in the code (based on the screenshot you sent)

     

    try this and don't change anything (copy past):

     

    Rankx = 
RANKX (
    FILTER ( ALL ( 'Sales' ), [Customer] = EARLIER ( 'Sales'[Customer] ) ),
    [Sales],
    ,
    DESC,
    DENSE
)

     

    Output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

7 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    Try this to add a Rankx column:

    Rankx = 
RANKX (
    FILTER ( ALL ( 'Table' ), [Customer] = EARLIER ( 'Table'[Customer] ) ),
    [Sales],
    ,
    DESC,
    DENSE
)

     

    then this to add Top N + Other column:

    Top N + Other = 
if([Rankx]<=3,[Product],"Other")

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!


    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi VahidDM 

       

      thanks for your advise. However, the RANKX formula doesn't work. It came out as all "1". Basically I would like to know the top 3 products for each of the customers and the rest will be grouped as "Others" for each customer. How should I get around with that?

       

      Thank you!

      • VahidDM's avatar
        VahidDM
        Super User
        4 years ago

        HI Anonymous 

         

        Can you copy and past a sample of your data table here (in table format to be able to copy and past that)?

         

        Those Codes in my previous post are need to be used to add new columns.

         


        Appreciate your Kudos!!
        LinkedIn:         www.linkedin.com/in/vahid-dm/