Rank country based on filtered value in card from two tables

[Loran]

Hello all,

I want to have two Cards showing the respective export and import rank of a country based on value I have filtered for in a slicer. The country and value are in two different tables, but they are linked in a model by ID:

Table 1

[TradeFlow][Value][ID]

Export	100	CA
Export	200	CB
Export	50	CC
Import	200	CA
Import	180	CB
Import	40	CC

Table2

[CountryName] [ID]

CountryA	CA
CountryB	CB
CountryC	CC

I can only do the following two measures, with the second measure (Overall Rank) as value in the Card.

Normal Rank =

RANKX(ALLSELECTED('Table1'[ID]),CALCULATE(SUM('Table1'[Value])),,DESC)

Overall Rank =

RANKX(ALL('Table1'[ID]),CALCULATE(SUMX('Table1','Table1'[Value])),,DESC)

But in this way, the slicer can only be ID and not CountryName, while I need it to be CountryName. I also need the data to be filtered so one slicer shows the export rank of the country while the other one shows the import rank of the country. Who could please help me?

Thanks!

[Ashish_Mathur]

Hi,

Based on the data tables that you have shared, show the expected result in different fiter scenarios. 

[lbendlin]

something like this?

[Loran]

Thanks for sharing this! While it works for the sample file you shared, when I use the exact same formulas you used for the real data, the card show the wrong ranks. Please find below a screenshot where you will see that Algeria is not part of the top 12 countries, but when I filter for Algeria in the slicer, it shows the wrong rank. Moreover, I would like to show the correct rank also for when I apply another filter, like year or product, when I apply these filters. So the right export and import rank should be shown when I filter not only for country, but also for a specific year and/or product I put in the slicer. Would this be possible?

Many thanks!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.