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 ("To...
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/

     

     

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/

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi VahidDM 

        Here you go!

         

        CustomerProductSales
        AllyProduct A           1,000
        AllyProduct D               200
        AllyProduct A           1,000
        AllyProduct B               300
        AllyProduct B               300
        AllyProduct C                 50
        JasonProduct A           1,000
        JasonProduct A           1,000
        JasonProduct A           1,000
        JasonProduct A           1,000
        JasonProduct B               200
        JasonProduct C               100
        JasonProduct D                 60

         

        Below is the screenshot of outcome in PowerBI:

         

        The outcome i desire is as below:

         

        Appreciate your help as i am very new to PowerBI and DAX!

         

        Thank you!!