Row number over rank column

[Anonymous]

I need to find top 10 sales as per Brand  and Item.so consider in power bi table i have 3 column Brand ,Item ,sales. Now i have created a rank measure using rankx function,now the problem is 7 records coming as rank one,5 records coming as rank 2 as the sales values are same..thats correct behavior of rank but when i need to show top 10 that time need to show only 10 rows not more than that.

Examplw below:

brnd,itm,sales,Rank, row(Rank)=>i want this clumn

a  aa    100       1       1

a  ab    100       1        2

a  ac     100       1        3

b  ad     80        4       4

b  ac      70       5       5

c  da     70        5       6

d  ee     70       5       7

e  ff      60       8       8

f  gf      60      8        9

f  bf      60      8       10

g  bf     60      8        11

e  fg     45      12      12

If i try to show top 10 rows using rank column it will return till my 11th rows,that is not my req. so if i can create a row number over rank then i can filter 10 records. any solution using DAX ??Thanks in Adv

[Anonymous]

Hi  @Anonymous ,

Create a calculated column as below:

Rank = RANKX('Table',RANKX('Table','Table'[sales],,DESC,Dense)+RANKX('Table','Table'[brand],,ASC,Dense)/10+RANKX(FILTER('Table','Table'[brand]=EARLIER('Table'[brand])),'Table'[item],,ASC,Dense)/100,,ASC,Dense)

And you will see:

Then create a measure to get the top 10:

Top 10 = 
IF(MAX('Table'[Rank])<=10,MAX('Table'[Rank]),BLANK())

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[Anonymous]

Hi @Anonymous ,Thank You.. But is it possible without creating any calculated column only we can create measure. because table level filter also need to keep and that time topn wont work.

[Anonymous · Accepted solution]

Hi @Anonymous ，

Create 2 measures as below:

Measure = 
var rank1=RANKX(ALL('Table'),CALCULATE(SUM('Table'[sales])),,DESC,Dense)
Return
RANKX(ALL('Table'),rank1+RANKX(ALL('Table'),CALCULATE(MAXX('Table','Table'[brand]&'Table'[item])),,ASC,Dense)/COUNTROWS(ALL('Table')),,ASC,Dense)

_TOP 10 = IF('Table'[Measure]<=10,'Table'[Measure],BLANK())

Then you will see:

For yhe related .pbix file,pls click here.

 
Best Regards,
Kelly

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

[Anonymous]

Hi @Anonymous  Thanks.

[FrankAT]

Hi @Anonymous,

what you are looking for is ranking without ties. Here is my solution with your sample data for a calculated column. In Query editor I added a PrimaryKey column. Whatever values each value in this column is distinct.

Regards FrankAT

[Anonymous]

Hi @FrankAT If I use this ,other filter(slicers) is not slicing this table.So the aim is not creating any table just crate a measure on top of rank and use that in filter.

[harshnathani]

Hi @Anonymous ,

Have a look at this video.

https://www.youtube.com/watch?v=kRtmb8ftyQc

You can break the ranks and then apply filters in the visuals.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ryan_mayu]

@Anonymous 

maybe you can try topn to create a new table. you can sort by more than one column.

T = TOPN(10,'table (2)','table (2)'[sales],DESC,'table (2)'[brand],ASC,'table (2)'[item],ASC)

[Anonymous]

thanx :). But i can not create new table i have to use same one because there are 50 columns in my dataset table i need to create a matrix from that single dataset table which is alraedy present just i can create dax.