RANKX in a measure

[Nicolas__]

Hi guys, 

After hours of searching and trying, I decided to come for your help.

I would like to rank my data depending on the filters I've put (series in rows, countries in column, rank in value) - filters are on countries and series: 

Series	Country A	Country B
A	1	2
B	1	2
C	2	1
D	2	1

If I add a country to my filters, then the ranking needs to be updated. Same for the serie.

Series	Country A	Country B	Country C
A	1	2	3
B	1	3	2
C	3	2	1
D	2	3	1

The data behind look like this: 

Series	Country	Value
A	Country A	10
A	Country B	5
A	Country C	0
B	Country A	10
B	Country B	0
B	Country C	2
C	Country A	0
C	Country B	20
C	Country C	30
D	Country A	20
D	Country B	5
D	Country C	30

Any hint please?

I've tried RANKX with measures, etc., but can't crack this so far.

Thank you, 

Nicolas

[Nicolas__]

A big big thank you @OwenAuger , it's working

Now I need to understand why I didn't find it on my own and analyze how it works 🙂

[OwenAuger · Accepted solution]

Hi @Nicolas__ 

I would recommend using the RANK function.

I have attached a sample PBIX with these measures as an example:

Value Sum = 
SUM ( Data[Value] )

Rank by Country = 
RANK (
    DENSE,
    ALLSELECTED ( Data[Country] ),
    ORDERBY ( [Value Sum], DESC BLANKS LAST )
)

There may be some adjustments needed depending on details of your model, but I would recommend a measure similar to Rank by Country above.

Does this work for you?

Regards