Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

Top Items (Comparison / Lookup)

Hello,
 
I have a TOP N with measures to rank by sales ($) each product. It can rank as COMPANY-wide or per Salesperson (like BOB).
 

 

 
What would be the correct measure or column to add to show that Bob is missing out on the top item/s of the COMPANY?  
 
In the example below, I would like to show that Bob is NOT selling Apple and Strawberry.  In other words, I would like to show what items are NOT in Bob's list compared to COMPANY.  Thanks.
 
 

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Oros ,

    The Table data is shown below:

    Please follow these steps:

    1. Use the following DAX expression to create a column in table 'BOB'

    Rank = RANKX('BOB',[Sales],,DESC,Skip)

    2.Use the following DAX expression to create a column in table 'COMPANY'

    Rank = RANKX('COMPANY',[Sales],,DESC,Skip)

    3.Use the following DAX expression to create a measure

    Measure = 
VAR _Top5_BOB = SELECTCOLUMNS(FILTER('BOB','BOB'[Rank] <= 5),"Top 5",[Product])
VAR _Top5_COMPANY = SELECTCOLUMNS(FILTER('COMPANY','COMPANY'[Rank] <= 5),"Top 5",[Product])
VAR _Result = ADDCOLUMNS(_Top5_COMPANY,"samewithBOB",IF([Top 5] IN _Top5_BOB ,BLANK(),[Top 5]))
RETURN CONCATENATEX(FILTER(_Result,[samewithBOB] <> BLANK()),[samewithBOB],",")

    4.Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Oros ,

    The Table data is shown below:

    Please follow these steps:

    1. Use the following DAX expression to create a column in table 'BOB'

    Rank = RANKX('BOB',[Sales],,DESC,Skip)

    2.Use the following DAX expression to create a column in table 'COMPANY'

    Rank = RANKX('COMPANY',[Sales],,DESC,Skip)

    3.Use the following DAX expression to create a measure

    Measure = 
VAR _Top5_BOB = SELECTCOLUMNS(FILTER('BOB','BOB'[Rank] <= 5),"Top 5",[Product])
VAR _Top5_COMPANY = SELECTCOLUMNS(FILTER('COMPANY','COMPANY'[Rank] <= 5),"Top 5",[Product])
VAR _Result = ADDCOLUMNS(_Top5_COMPANY,"samewithBOB",IF([Top 5] IN _Top5_BOB ,BLANK(),[Top 5]))
RETURN CONCATENATEX(FILTER(_Result,[samewithBOB] <> BLANK()),[samewithBOB],",")

    4.Final output

     

    Best Regards,
    Wenbin Zhou
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Oros's avatar
      Oros
      Post Prodigy
      2 years ago

      Hi Anonymous,

       

      Thank you so much.  This works!!!