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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply

rank Always returning 1 for all the brands

I have approx 32 brands in my dataset, I need to show top 5 brands

Sample

Brandaverage salesRank
A801
B811
C821
D871
E871
F901
G951
H981

 

Formula using 

RANKX(filter(ALLSELECTED('PPI Details'[Brand]),Round([Average Sales],0)<>0),Currency([Average Sales]),,ASC,Skip))
It is returning 1 as rank of all the rows,
(Round([Average Sales],0)<>0 used to exclude 0 values for ranking)
 
But when I create a calculated column (in same table) referring existing Brand column and use that in visual and formula it is returning the right result, can somebody please help me to understand this
 
1 ACCEPTED 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

View solution in original post

7 REPLIES 7

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

Brandaverage salesRank
A801
B812
C823
D874
E875
F906
G957
H988

  

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.