Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have approx 32 brands in my dataset, I need to show top 5 brands
Sample
Brand | average sales | Rank |
A | 80 | 1 |
B | 81 | 1 |
C | 82 | 1 |
D | 87 | 1 |
E | 87 | 1 |
F | 90 | 1 |
G | 95 | 1 |
H | 98 | 1 |
Formula using
Solved! Go to Solution.
Thank you @Ashish_Mathur for help, seems like it was because of some cache issue.
what i did is I deleted the brand column in power query editor, applied the step then removed the step of removed column for Brand column then close and apply and it worked
Hi,
Drag Brand to the table visual and writ these measures:
Avg sales = average(Data[average sales])
Measure = CALCULATE([Avg sales],topn(5,ALL(Data[Brand]),[Avg sales]),VALUES(Data[Brand]))
Hope this helps.
Not working, still returning all the brands
As you can see in my screenshot and PBI file, my solution work fine. Share the download link of your PBI file.
Sorry Ashish, Can't share the PBIX file because of client data security and when i recreate the same issue with dummy data I am not getting this issue, my rank formula works fine.
As I said my formula works even if I create a calculated column for Brand referring existing column in same table and use that in visual and rank formula.
Example :
I create a Brand_New calculated column which is Brand_New = Data[Brand]
and rank formula=
RANKX(filter(ALLSELECTED('Data'[Brand_New]),Round([Average Sales],0)<>0),Currency([Average Sales]),,ASC,Skip))
Average Sales = average(Data[Sales])
then
Brand | average sales | Rank |
A | 80 | 1 |
B | 81 | 2 |
C | 82 | 3 |
D | 87 | 4 |
E | 87 | 5 |
F | 90 | 6 |
G | 95 | 7 |
H | 98 | 8 |
Hi,
This should be writen as a mesure (not as a caluclated column) because measures will repond to a change in slicers (calculated column formulas will not). Share the download link of your anonymised dataset.
Thank you @Ashish_Mathur for help, seems like it was because of some cache issue.
what i did is I deleted the brand column in power query editor, applied the step then removed the step of removed column for Brand column then close and apply and it worked
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
84 | |
73 | |
49 |
User | Count |
---|---|
143 | |
131 | |
110 | |
64 | |
55 |