Ranking a Country within Continents with data at a City level using a lookup table.

[Anonymous]

Hey, trying to solve this one for a while now. Lots of solutions that come close but don't exactly do what I need it to.

I have a data table "city data" (Month, City, Total Spend, Park Spend) and a lookup table "lookup" (City, Country, Continent) connected on City.

My goal is to have a drop down for Country that will update a card visual (not table) with the that Country's rank within the Continent based on a measure - let's say Park Spend % = Sum of Park Spend / Sum of Total Spend. This measure can change, I will use a SWITCH in my ranking solution to allow the basis metric/measure to be dynamic. For the sake of this problem let's assume I have a measure Park Spend %.

I have screenshots and a sample of the tables and goal below.

Any assistance will be appreciated!

data

lookup

goal

city data

Month	City	Total Spend	Park Spend
1	A	91.96	42.46
1	B	77.04	45.64
1	C	80.35	49.78
1	D	79.12	35.73
1	E	98.6	45.97
2	A	90.93	45.59
2	B	99.1	47.54
2	C	93.62	43.26
2	D	92.06	43.99
2	D	78.51	46.31
3	A	99.15	46.42
3	B	86.17	38.78
3	C	86.96	39.27
3	D	78.98	39.54
3	E	90.81	35.36
4	A	75.5	47.81
4	B	88.62	37.74
4	C	75.38	37.78
4	D	98.14	42.25
4	E	82.81	38.54

lookup

City	Country	Continent
A	US	North America
B	US	North America
C	Canada	North America
D	Canada	North America
E	UK	Europe

[Whitewater100]

Hi:

How does this look? Link for report..

https://drive.google.com/file/d/19GDshsKGvCHcrg_fbJCe1L5sn5GGSMAt/view?usp=sharing 

[Anonymous]

When I select UK, country rank returns 3, while it should return 1 since UK ranks 1 in Europe. Like I mentioned, I would like to see the Country Rank within it's Continent on the card.

[littlemojopuppy]

@Anonymous please refer to the solution I posted an hour before @Whitewater100

[Anonymous]

Yes, I saw your response and am evaluating it.

[littlemojopuppy · Accepted solution]

Hi @Anonymous 

Try this...

Country Ranking = 
VAR CountriesInContinent =
    CALCULATETABLE(
        VALUES(Geography[Country]),
        FILTER(
            ALL(Geography),
            Geography[Continent] = SELECTEDVALUE(Geography[Continent])
        )
    )
RETURN

RANKX(
    CountriesInContinent,
    [Park Spend %],
    [Park Spend %],
    DESC,
    Skip
)

Produces these results

The matrix was to make sure each country was calculating correctly within its continent...

Hope this helps!

[Anonymous]

@littlemojopuppy  Thanks for your response it does what I asked for and I can see has already been marked as the solution.

However, in my actual dataset, applying any date slicer from a connected calendar table causes the rank to  be funny when in a card but populates correctly against the country in a table. No idea why that would happen. Even if I select all on my date slicer, that is nothing is excluded, my rank in the card is wrong, But if I don't engage the slicer at all - it works fine. Do you have any thoughts?

[littlemojopuppy]

@Anonymous how are you creating the relationship between the date table and this table?  Because the sample data you shared only has month number (and no year).

Could you share your pbix?

[Whitewater100]

Hi littlemojopuppy:

When I saw this quesition yesterday, no one had replied on it. I went to work on it and posted my answer as soon as I finished,  still without seeing your answer until today. 

Sorry for any confusion.

When did you answer and when did I, as I did my answer fairly quickly after seeing the question?

[littlemojopuppy]

@Whitewater100 you posted your response an hour after I did.  I've noticed you doing this on multiple posts in the last couple weeks, both to me and to others.  There's an expectation of etiquette in the forum, where we try not to overwhelm the users with numerous responses.  Because all it does is create confusion.  It would be appreciated if you followed etiquette.