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
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
Solved! Go to 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.
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.
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.
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.
Hi @Anonymous Thanks.
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
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.
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)
@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!
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
102 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |