Forum Discussion

Sameer94's avatar
Sameer94
New Member
6 years ago
Solved

Ranking issue ... Please help

Hi,

 

I have two tables with me. One is table 1, which has a column "Market" in it, and another table is table 2, which has "Customer" in it. Table 1 is linked with table 2 via Customer ID (Many to many relationship). Now, I have created a measure that is calculating sum of sales (Column sales present in table 1). Now what I need is, I have to create a table (visual) with 4 columns : First one being the "Market" (coming from table 1), second one being "Rank of customer" in that market based on total sales, third being the "Customer", forth being "Total Sales". I have attached an excel screenshot for what I need:

 

I want help creating ranking measure to achieve the same. Please note, I dont want to create a table in backend as the measure total sales will be dynamic ( to calculate current month, ytd, mat values), hence I want this to be in a table visual.

Please help, I m stuck for a day now, and now looking for experts.

 

Thanks a lot, hope I was as clear as possible.

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Sameer94 ,

     

    You Sheet 2 has customer id C_2 for multiple customers. Pls check that,

     

    You will need to create a Calculated Colum for Lookup of Customer from Sheet 2.

     

    Customer ID From sheet2 =

    VAR SearchValue = Sheet1[Customer_ID]
    RETURN
    CALCULATE (
    SELECTEDVALUE ( Sheet2[Customer Name], "a" ),
    FILTER (
    ALLNOBLANKROW ( Sheet2[Customer_ID] ),
    Sheet2[Customer_ID] == SearchValue
    ),
    ALL ( Sheet2 )
    )

     

     

    then create 2 Measures

     

    Sum of Value = CALCULATE(Sum(Sheet1[Value]), ALLEXCEPT(Sheet1,Sheet1[Customer ID From sheet2]))
     
    Ranking = IF(NOT(ISBLANK([Sum of Value])),RANKX(FILTER(ALL(Sheet1[Customer_ID],Sheet1[Market],Sheet1[Customer ID From sheet2]), Sheet1[Market] = MAX(Sheet1[Market]) ),[Sum of Value]))
     
     
     
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Sameer94 ,

     

    Please share smaple data of both tables.

     

    Regards,

    HN

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    Sameer94 

     

    you can create two measures

    total = CALCULATE(sum(tbl1[Sales]),ALLEXCEPT(tbl1,tbl1[market],tbl1[customer]))

ranking = 
RANKX(ALLEXCEPT(tbl1,tbl1[market]),[total],,DESC,Dense)

     

    However, I am stuck on the sorting. I think you want to group by market and sort by ranking.

     

    Let's see if someone else can help you on that.

     

     

     

    • Sameer94's avatar
      Sameer94
      New Member
      6 years ago

      I am trying to upload a pbix file but cant find any option to upload ... The thing is as you did ... the Customer column is in same table ... for me its in different table (table 2) and as I dont want to do Lookupvalue () coz the original pbix file is already 3gb. Can you let me know how to upload a sample pbix file here ?

       

      Please follow the link to get to the sample pbix file (and do not bring market and customer columns from different tables into one table through query editor or via Data section, they both will be together in table/matrix visual only ) :

      https://drive.google.com/file/d/19Ihh00kzK-zYbqH68soDNsQp3L0yajmT/view?usp=sharing

       

      Thanks for replying

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Sameer94 ,

     

     

     

    Create 2 measures

     

    Sum of Value = CALCULATE(Sum(Sheet1[Value]), ALLEXCEPT(Sheet1,Sheet1[Customer_ID]))
     
    Ranking = RANKX(FILTER(ALL(Sheet1[Market],Sheet1[Customer_ID]), Sheet1[Market] = MAX(Sheet1[Market])),[Sum of Value])
     

    Regards,
    Harsh Nathani

    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

    • Sameer94's avatar
      Sameer94
      New Member
      6 years ago

      Hi Anonymous ,

       

      The solution you mentioned was so close that it literally got me excited, however the catch is if I bring Customer Name into the table visual created, the system breaks, the ranking breaks. Can you get the code in such a way that I can get the corresponding customer name next to the customer ID or only just the customer name, and the ranking still works ?

       

      Thanks a lot sir.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Sameer94 ,

         

        You Sheet 2 has customer id C_2 for multiple customers. Pls check that,

         

        You will need to create a Calculated Colum for Lookup of Customer from Sheet 2.

         

        Customer ID From sheet2 =

        VAR SearchValue = Sheet1[Customer_ID]
        RETURN
        CALCULATE (
        SELECTEDVALUE ( Sheet2[Customer Name], "a" ),
        FILTER (
        ALLNOBLANKROW ( Sheet2[Customer_ID] ),
        Sheet2[Customer_ID] == SearchValue
        ),
        ALL ( Sheet2 )
        )

         

         

        then create 2 Measures

         

        Sum of Value = CALCULATE(Sum(Sheet1[Value]), ALLEXCEPT(Sheet1,Sheet1[Customer ID From sheet2]))
         
        Ranking = IF(NOT(ISBLANK([Sum of Value])),RANKX(FILTER(ALL(Sheet1[Customer_ID],Sheet1[Market],Sheet1[Customer ID From sheet2]), Sheet1[Market] = MAX(Sheet1[Market]) ),[Sum of Value]))
         
         
         
         

        Regards,
        Harsh Nathani

        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)