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...
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)

     

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)