rank Always returning 1 for all the brands

[raghavdevsingh]

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 

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

@Ashish_Mathur Please help @PBICommunity 

[raghavdevsingh]

@amitchandak @PBICommunity @Jihwan_Kim @mscottsewell @lbendlin Please help

[Ashish_Mathur]

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.

[raghavdevsingh]

Not working, still returning all the brands

[Ashish_Mathur]

As you can see in my screenshot and PBI file, my solution work fine.  Share the download link of your PBI file.

[raghavdevsingh]

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

[Ashish_Mathur]

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.

[raghavdevsingh · 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