Ranking and filetr for top N, for matrix table using DAX

[Zakk2000]

Hi All,

I am trying to do ranking for the inner row for the matrix table and need help where I am stuck for several days on this.

Sample Data:

Country	Area	Sales
US	A	$
US	B	$
Australia	G	$
US	C	$
US	D	$
China	H	$
US	E	$
US	F	$
US	G	$
China	H	$
China	I	$
China	J	$
US	A	$
China	K	$
China	L	$
China	M	$
Australia	N	$
Australia	O	$
US	A	$
Australia	P	$
Australia	Q	$
Australia	R	$
Australia	S	$

I used this DAX function:

Rank = RANKX (ALL (Report[Area]), CALCULATE(SUM(Report[Sales])))

and use filter to filter up top 3 sales.

 

==============================================

Result Received:

Country/Area	Sales	Rank (Result Received)
US	490000	1
A	100000	1
G	90000	1
C	80000	1
D	70000	3
E	60000	3
F	50000	4
B	40000	1
China	330000	2
H	80000	1
M	70000	1
J	60000	1
K	50000	1
L	40000	1
I	30000	1
Australia	210000	3
N	60000	1
S	50000	2
P	40000	1
G	30000	1
R	20000	1
O	10000	4

Intended Result:

Country/Area	Sales	Intended result
US	490000	1
A	100000	1
G	90000	2
C	80000	3
China	330000	2
H	80000	1
M	70000	2
J	60000	3
Australia	210000	3
N	60000	1
S	50000	2
P	40000	3

Appreciate all the help on this 🙂

[FreemanZ]

hi @Zakk2000 

try like:

RANKX(

    ALL(Report[Area]),

    CALCULATE(SUM(Report[Sales])),

    ,

    ,

    Dense

)

[Zakk2000]

Hi @FreemanZ ,

I tried your suggestion, the result is kinda the same but filter out more data.

I still have multiple "area" (rows) ranking 1,2 and 3.

I also forgot the mention, the data set provided here is the final version after being transformed in the query.