Top 3 Clients based on Amount by Country

[PBI_newuser]

Hi, how to show only top 3 clients by count based on the amount? I used below measure but it doesn't work.

Sample here.

Rank - by Country by Client =
IF (
    ISINSCOPE ( D_Client[Client]),
    RANKX (
        CALCULATETABLE (
            VALUES (   D_Client[Client] ),
            ALLSELECTED (   D_Client[Client] )
        ),
        SUM('Table'[Amount])
    )

)

Expected output:

Country	Client	Amount	Rank - by Country by Client
AU	C-1	615711	1
AU	C-2	248835	2
AU	C-3	60560	3
HK	C-37	349800	1
HK	C-35	67238	2
HK	C-38	48684	3
ID	C-71	152828	1
ID	C-72	130672	2
ID	C-73	33982	3
KH	C-80	306416	1
KH	C-81	252829	2
KH	C-82	143556	3
LA	C-82	73693	1
LA	C-83	23046	2
LA	C-110	5646	3
MM	C-115	151217	1
MM	C-116	35297	2
MM	C-117	28282	3
MY	C-83	1664764	1
MY	C-42	1607266	2
MY	C-128	1577619	3
NZ	C-128	423115	1
NZ	C-180	220549	2
NZ	C-181	131350	3
SG	C-223	301638	1
SG	C-50	240292	2
SG	C-224	180594	3
TH	C-114	560730	1
TH	C-133	512305	2
TH	C-228	468016	3
TW	C-83	363978	1
TW	C-311	26649	2
TW	C-109	25356	3
VN	C-108	628540	1
VN	C-82	390713	2
VN	C-137	311803	3

[tharunkumarRTK · Accepted solution]

@PBI_newuser 

Here is one way to achieve the result 

Top3 countries =

var result = RANK(DENSE, SUMMARIZE(ALLSELECTED('Table'),'Table'[Country],D_Client[Client]) , ORDERBY(CALCULATE(SUM('Table'[Amount])),desc), ,PARTITIONBY('Table'[Country]))

return IF(result <=3 , result)

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[PBI_newuser]

Thanks @tharunkumarRTK , it works!

[zenisekd]

Hi, could you try: 
Top3ClientsByCountry =
VAR RankedClients = ADDCOLUMNS ( SUMMARIZE ( Sales, Sales[Country], "RankedClients", TOPN ( 3, SUMMARIZE ( FILTER ( Sales, Sales[Amount] > 0 ), Sales[Client], "TotalAmount", SUM(Sales[Amount]) ), [TotalAmount], DESC ) ), "Client", [RankedClients] )
RETURN RankedClients

Kudos and mark as solution appreciated 🙂

[PBI_newuser]

Hi @zenisekd , thank you for your suggested solution but  it doesn't work.