RANKX returning all ranks as 1

[Anonymous]

I'm trying to us RANKX so that I can show the top 5 or so values in my data. Somewhere along the way, I've made a mistake. The RANKX function is only returning "1" for every single entry. At first, I thought this was a "filter context" issue so I wrapped the "table" value in an ALL function. No change.

This is the current form of my code:

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	SUM('Weekly Summary'[Impressions]),
	,
	ASC
)

I read on a post here that my code could be incorrect because of the filter context of the actual expression--AKA the "SUM('Weekly Summary'[Impressions])"--so I need to wrap the expression value in a CALCULATE function. So this is what I tried to do:

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	CALCULATE(
                SUM('Weekly Summary'[Impressions]),
                ALL('Weekly Summary'[Impressions])),
	,
	ASC
)

This also did not work (rank was still returning all "1").

Does anyone know how my snippets of code are incorrect?

[Sean · Accepted solution]

@jmoore1995

CALCULATE is needed to perform the context transition for each row of the table

otherwise the filter context is always the same and all items will rank 1

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	CALCULATE ( SUM('Weekly Summary'[Impressions]) ),
	,
	ASC
) 

However if you already have a Measure for SUM( 'Weekly Summary'[Impressions] ) then you don't need the CALCULATE

rankImpressions = 
RANKX(
	ALL('Weekly Summary'),
	[MEASURE],
	,
	ASC
) 

Hope this helps!

[Anonymous]

Thanks @Sean! You're quickly becoming one of my favorite people on these forums!

[manjirit]

I have a similar problem and can't seem to find whats going wrong! The RankX function gives two distinct ranks to the rows viz. 1 and the highest rownumber (eg. 20,000) 

Here is what my formula looks like:

Rank = RANKX (all(TableName),[All_Assets])  --- gives rank = 1 to  rows with All_assets > 0 and  rank =maxRank (something like   20,000)  to rows with All_assets = 0 

 I also tried: Rank = RANKX(all(TableName),calculate([All_Assets])) -- gives me the same result. And anyway I don't think I need to include Calculate since " All_assets" is a  measure which is precalculated.

All_Assets is a measure which is calculated as follows:

All_Assets = DISTINCTCOUNT(TableName[AssetName])

Any idea, what is going on?