How to top 10 rows when there are duplicate values for Columns in Table Visual?

[Anonymous]

Hi All,
Please find below Sales table which should show top 10 rows based on Deal names for Clients
Client Column in original table are around 50 and Deal Name column has around 1200 Distinct Values
I want to show Top 10 rows based on Deal Name for the below table. Kindly suggest !

Client	Deal Name	Revenue
ABC	Deal Number 1	500M
XYZ	Deal Number 2	490M
MNO	Deal Number 1	490M
ABC	Deal Number 3	400M
XYZ	Deal Number 2	380M
PQR	Deal Number 1	300M

[Pragati11]

Hi @Anonymous ,

Can you share some sample output that you are expecting?

You can try adding an index column in your data and see if that works.

Thanks,

Pragati

[Anonymous]

Hi Pragati,
It should look like this
Client name Deal Name Revenue
A                  ABC              500m
B                  ABC              500m
C                  pqr                500m
E                  MNO             300M
F                  MNO             300M

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

Create 2 columns as below:

_count = 
var _rank1=RANKX(ALL('Table'),'Table'[Client],,ASC,Dense)
var _rank2=RANKX(ALL('Table'),'Table'[Deal Name],,ASC,Dense)
var _rank3=RANKX(ALL('Table'),'Table'[Revenue],,ASC,Dense)
var _count=CALCULATE(COUNTROWS('Table'),ALL('Table'))
Return
_rank1+DIVIDE(_rank2,_count)+DIVIDE(_rank3,_count*10)

rank = RANKX('Table','Table'[_count],,ASC,Dense)

And you will see:

Finally filter out the lines which rank value is larger than 10.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , based on need on of the two rank should help, use a visual level filter <=10

rankx(allselected(Table[client], Table[Deal name]), [revenue],,desc,dense)

or

rankx(filter(allselected(Table[client], Table[Deal name]),Table[client] =max(Table[client])), [revenue],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Anonymous]

Hi Amit,
Rankx is giving same rank to all clients and dealname when revenue value is same for 4 or 5 rows, how to handle that?
I want to show top 10, it is okay if the revenue value is same for them.
Kindly suggest