March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |