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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.