topic Re: Top Items (Comparison / Lookup) in DAX Commands and Tips

Top Items (Comparison / Lookup)

Oros — Tue, 25 Jun 2024 16:07:52 GMT

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.

Re: Top Items (Comparison / Lookup)

Anonymous — Wed, 26 Jun 2024 01:56:45 GMT

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.

Re: Top Items (Comparison / Lookup)

Oros — Tue, 02 Jul 2024 11:40:14 GMT

Hi Anonymous,

Thank you so much. This works!!!