Need to add rank in table

[Anonymous]

Hi All,

I have data like below table, i want to add Top N Invoice Filter

output : want to create Rank by Invoice, and also want to add Rank and Top N filter in below Table

Thanks,

Vengadesh P

[v-shex-msft · Accepted solution]

Hi @Anonymous,

I think you need a unique field that used to combine with the amount field for ranking or it is hard to accurately filter only top n records. (for the same values)

Add an index column (Power Query) - Excel (microsoft.com)

Sorting by multiple columns 

Regards,

Xiaoxin Sheng

[DataVitalizer]

Hi @Anonymous 

Create these two calculated columns and you will get the desired result:

Rank= RANKX(Table,Table[Sales AMT])
Top 5 Filter= VAR c=RANKX(ALL(Table),Table[Rank],,ASC) return IF(c<=5,1,0)

Did it work ? Mark it as a solution to help spreading knowledge.

A kudos would be appreciated  

[negi007]

@Anonymous You can create a custom column in your data like below

Rank = RANK.EQ('Table'[Value],'Table'[Value],DESC)

Rank_top = if('Table'[Rank]<=5,"Yes","No")