cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.