topic Re: Ranking a Country within Continents with data at a City level using a lookup table. in DAX Commands and Tips

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

Anonymous — Sun, 06 Mar 2022 00:12:25 GMT

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!

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

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

littlemojopuppy — Sun, 06 Mar 2022 00:51:07 GMT

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!

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

Whitewater100 — Sun, 06 Mar 2022 01:14:15 GMT

Hi:

How does this look? Link for report..

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

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

Anonymous — Sun, 06 Mar 2022 10:37:43 GMT

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.

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

littlemojopuppy — Sun, 06 Mar 2022 11:51:30 GMT

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

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

Whitewater100 — Sun, 06 Mar 2022 13:18:48 GMT

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?

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

littlemojopuppy — Sun, 06 Mar 2022 13:32:10 GMT

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.

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

Anonymous — Sun, 06 Mar 2022 18:43:31 GMT

Yes, I saw your response and am evaluating it.

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

Anonymous — Sun, 06 Mar 2022 18:50:09 GMT

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?

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

littlemojopuppy — Sun, 06 Mar 2022 19:31:18 GMT

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?