Calculate ranks dynamically based on Slicer from related table

[AceSuarilla]

I am trying to do something similar to the one on this link: https://community.powerbi.com/t5/Community-Knowledge-Base/Calculate-ranks-dynamically-based-on-Slice...

Only difference is that the slicer is a column from a related table. 

Ex. I'm trying to rank the total sales per country, but I have a slicer for continent on a separate geo table. 

What I'm getting is that when I filter the data to say, Asia, the rest of the countries from the other continents are still there but they just have the same value.

EX: 

Country	Continent	Rank
Japan	Asia	1
Indonesia	Asia	2
Germany	Europe	3
Argentina	South America	3

I realize that I can always just add the continent to the same table and use that as the slicer, but I'm curious to know how I could do that with the current setup.

[v-yuta-msft]

Hi AceSuarilla,

Try this:

rank =
RANKX (
    CALCULATETABLE ( SaleTable, ALLSELECTED ( geo ) ),
    CALCULATE (
        SUM ( SaleTable[sales] ),
        ALLEXCEPT ( SaleTable, SaleTable[country] )
    ),
    ,
    DESC
)

Regards,

Jimmy Tao

[AceSuarilla]

Hi @v-yuta-msft,

That unfortunately didn't do it. 

From this sample:

SALES:

Sales	Country
550	Japan
221	Indonesia
543	Germany
112	Argentina

GEO: 

Country	Continent
Japan	Asia
Germany	Europe
Indonesia	Asia
Argentina	South America

What I got were these:

Filtered to Asia:

Sales	Country	Rank
550	Japan	1
221	Indonesia	2
	Germany	2
	Argentina	3

Filtered to Europe:

Sales	Country	Rank
	Japan	1
	Indonesia	2
	Germany	1
112	Argentina	2

Filtered to South America:

Sales	Country	Rank
	Japan	1
	Indonesia	1
	Germany	1
112	Argentina	1