Dynamic ranking by different fields

[jmateu88]

Hello,

I am trying to get the ranking of a sum by choosing the field but unfortunately, I do not know how to create a dynamic ranking dax.

Here I attach the data. As we can see, there are three fields of category and finally another with numbers which will be used to obtain a measury with the summatory.

PersonCountryItemAmount

Mike	Spain	shirts	1
John	France	pants	2
Peter	Italy	shoes	3
John	Greece	hats	4
Mike	Spain	belts	5
John	France	shirts	6
Susan	Italy	pants	7
Mike	Spain	pants	8
Peter	France	shoes	9
Sam	Germany	belts	10
Mike	Spain	hats	1
Susan	Italy	pants	2
John	Greece	shirts	3
Mike	France	belts	4
Peter	Spain	shoes	5
Sam	Germany	pants	6
Susan	Italy	belts	7
Mike	France	pants	8
John	Spain	hats	9
Sam	Greece	belts	10
Susan	Germany	shoes	1
Mike	Spain	shirts	2
John	France	belts	3
Sam	Italy	hats	4
Mike	Germany	pants	5
Susan	France	shoes	6
John	Greece	belts	7
Mike	Spain	hats	8
John	France	belts	9
Peter	Germany	pants	10
Sam	Spain	shirts	1
Susan	France	shoes	2

In order to be able to filter by field, I have created a parameter which allow us to choose which field use to obtain the summatory. 

However, I cannot get the ranking of these in a dynamic way. 

Here the parameter table created with all formulas used.

As we can see in the board, the dynamic ranking does not work as it woul be desired.

Is there a way to set it dynamically in order to avoid extra dax measures and have only one ranking column in the table?
Thank you in advance!

[shafiz_p · Accepted solution]

Hi, @jmateu88 Please use the below measure, which dynamically rank different fields.

Rank =

VAR _selectedValue = SELECTEDVALUE(Parameter[Parameter Order])

VAR _rankCountry = RANKX(ALLSELECTED('Table'[Country]), [TotalAmount])

VAR _rankItem = RANKX(ALLSELECTED('Table'[Item]), [TotalAmount])

VAR _rankPerson = RANKX(ALLSELECTED('Table'[Person]), [TotalAmount])

VAR _Result =

SWITCH(

    TRUE(),

    _selectedValue=0, _rankItem,

    _selectedValue=1, _rankPerson,

    _rankCountry

)

RETURN

_Result

Hope this helps!!

If this solved your problem, please accept it as a solution!!

[jmateu88]

Hello @shafiz_p and thank youv ery much  for the right answer!

The only detail to consider is that the 0 is for Country, 1 for Item and 2 for Person.