cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Anonymous
Not applicable

## Row number over rank column

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  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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

Best Regards,
Kelly
9 REPLIES 9
Anonymous
Not applicable

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:

Best Regards,
Kelly

Anonymous
Not applicable

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
Not applicable

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:

Best Regards,
Kelly
Anonymous
Not applicable

Hi @Anonymous  Thanks.

Community Champion

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
Not applicable

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.

Community Champion

Hi @Anonymous ,

Have a look at this video.

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)

Super User

@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)``

Proud to be a Super User!

Anonymous
Not applicable

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.