Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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:

Annotation 2020-06-29 123304.png

For yhe related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

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:

Annotation 2020-06-26 153735.png

Then create a measure to get the top 10:

 

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

 

And you will see:

Annotation 2020-06-26 153933.png

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 
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:

Annotation 2020-06-29 123304.png

For yhe related .pbix file,pls click here.

 

 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Anonymous
Not applicable

Hi @Anonymous  Thanks.

FrankAT
Community Champion
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.

 

26-06-_2020_00-00-04.jpg

 

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.

harshnathani
Community Champion
Community Champion

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
Super User
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)

1.PNG

 





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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.