Top Items (Comparison / Lookup)

[Oros]

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.

[Anonymous · Accepted solution]

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]

Hi @Anonymous,

Thank you so much.  This works!!!