Solved: RANKX and ALL Functions

RodgerB · ‎01-16-2020

I am struggling to figure out how to use the RANKX function. I've made my DAX work using

RANKX(ALLSELECTED(Invoices[Product Name]),CALCULATE([Total Sales]),,desc)).

What I don't understand is all the forums that tell you to use the ALL() function. Whenever I do, all I get is 1's. The above works, I just don't understand why one works and not the other.

Also, is there a way to have any dimension field not show a value, so just the rank results from the ALLSELECTED field display a value?

Thanks!

Anonymous · ‎01-16-2020

Hi Rodger,

The ALLSELECTED makes sure that you only consider the "selected" Invoice[Product Names] when the rank is calculated. When you use ALL, as you can guess, the total sales for all product names are considered and you may end up gaps in your ranking sequence. So normally this does not explain why you get 1's when using ALL, so that could be related to your data model and definition of [Total Sales].

But typically you get all 1's from RankX when you use the measure in the wrong context. For instance if you use you measure in a matrix with rows per country, you will get 1's.

The reasons is that the lookup table used for [total sales] contains the total sales amounts by country and by product and the lookup table used to determine the rank only contains values per country. Therefore the total per country (used for ranking) is always higher (or equal at best when there is just one product in the country) than any the total per product within a given country.

I also see that you use the parameter 'desc', which is not necessary because it is the default setting, and you wrap the [Total Sales] measure with CALCULATE which is not necessary since a measure already contains an implicit CALCULATE. So RANKX(ALLSELECTED(Invoices[Product Name]),[Total Sales]) should give the same result.

Hope this helps.

jan

View solution in original post

Anonymous · ‎01-16-2020

Hi Rodger,

The ALLSELECTED makes sure that you only consider the "selected" Invoice[Product Names] when the rank is calculated. When you use ALL, as you can guess, the total sales for all product names are considered and you may end up gaps in your ranking sequence. So normally this does not explain why you get 1's when using ALL, so that could be related to your data model and definition of [Total Sales].

But typically you get all 1's from RankX when you use the measure in the wrong context. For instance if you use you measure in a matrix with rows per country, you will get 1's.

The reasons is that the lookup table used for [total sales] contains the total sales amounts by country and by product and the lookup table used to determine the rank only contains values per country. Therefore the total per country (used for ranking) is always higher (or equal at best when there is just one product in the country) than any the total per product within a given country.

I also see that you use the parameter 'desc', which is not necessary because it is the default setting, and you wrap the [Total Sales] measure with CALCULATE which is not necessary since a measure already contains an implicit CALCULATE. So RANKX(ALLSELECTED(Invoices[Product Name]),[Total Sales]) should give the same result.

Hope this helps.

jan