RANKX Always Returns 1

[rajulshah]

I have 3 columns of Country ,Product and Sales. I created a measure to calculate rank for countries on the basis of total sales which gives me rank according to the country and product selected in the page level filter,

RankM = RANKX(ALLSELECTED(FTfinancials[Country]),[Total Sales],,DESC)
 where [Total Sales] is a measure I calculated earlier which basically calculates sum of Sales.

Here is the formula ,
Total Sales = SUM(FTfinancials[Sales])

This formula gives me exact answer as I want as below.

But when I write rank measure as RankM = RANKX(ALLSELECTED(FTfinancials[Country]),SUM(FTfinancials[Sales]),,DESC) where I just provided formula instead of the name of the measure , it gives me rank as 1 for every country as below ,

I don't understand it is same but I am still getting completely different answer.

Can anyone help me understand why is this happening? Why am I getting different answers when it's performing same operations?

Thanks in advance.

[OwenAuger · Accepted solution]

@rajulshah

When used in an expression, measures are automatically wrapped in CALCULATE(), so [Total Sales] is equivalent to CALCULATE ( SUM ( FTfinancials[Sales] ) ). To get the same result without referring to the measure, you must use CALCULATE.

Explanation:

The RANKX function iterates through the rows of the table provided in the first argument, and evaluates the expression provided in the second argument in the context of each row. This is then used as a basis for ranking. A lot of the time, you will want to wrap the second argument in CALCULATE or use a measure in order to convert the row context to filter context, so that the measure is evaluated in a filter context equivalent to that row (context transition).

If you don't wrap the second argument in calculate, it will just be evaluated in the current filter context, which in your case is the context of a single country, which would produce the same result for every row. Then, since no third argument is specified, this same expression will be evaluated in the current filter context, which will be identical to the value for every row calculated earlier, hence the rank of 1.

Look for some articles on RANKX or context transition for more detailed explanation 🙂

Regards,

Owen

[rajulshah]

Thank You @OwenAuger , it helped me and cleared my concepts. Thanks a lot.