cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TytlerJaxon
Frequent Visitor

Top and Bottom 3 Ranked Countries From Selected Country by GDP in Chart

Hi there,

 

I have a data set which contains GDP values for each country and for each year for a ten year period. I am trying to create a Line chart with "Year" across the X-axis, "GDP Values" along the Y-axis and country as the Legend. I would like to be able to select a specific country, say "United Kingdom" for example, and only have displayed the Top 3 countries above and Bottom 3 countries below the "United Kingdom" based on the current Years GDP Values (in this case 2017). 

 

So if Germany was higher than the UK in 2017 it would show Germany above immediately above the UK. I assume I need a complex measure of some kind. I have tried RANK and RANKX functions but can't seem to get the right result.

 

Any help would be much appreciated,

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Top and Bottom 3 Ranked Countries From Selected Country by GDP in Chart

Hi @TytlerJaxon,

 

In your scenario, you can create another table (Table2) contains the distinct Country value. Make sure there is no relation created between Table2 and the fact table. Create a measure in this Table2 to get selected value in slicer:

 

Select Value = IF(HASONEVALUE(Table2[Country]),VALUES('Table2'[Country]),BLANK())

 

Then create a calculated column in fact to rank GDP within each year group:

Rank = RANKX(FILTER('Table1','Table1'[Year]=EARLIER(Table1[Year])),'Table1'[GDP],,1)

 

Also create a measure in fact table:

 

Top/Bottom 3 ranked =
var curr=LOOKUPVALUE(Table1[Rank],'Table1'[Country],'Table2'[Select Value],'Table1'[Year],MAX('Table1'[Year]))
return
CALCULATE(SUM('Table1'[GDP]),FILTER('Table1','Table1'[Rank]<= curr+2 && 'Table1'[Rank]>=curr-2 && 'Table1'[Year]=MAX('Table1'[Year])))

 

Drag the Country column from Table2 to slicer, create a clustered column chart, select one value from the slicer.

 

p1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Top and Bottom 3 Ranked Countries From Selected Country by GDP in Chart

@TytlerJaxon This is an example I have from awhile ago, there may be ways to combine it which others may know, but this approach works for me and you can extrapolate top and bottom in the 3rd measure

 

Units Sold Total = SUM(table[Products Sold])
Units Sold Rank by Product = RANKX(ALLSELECTED(table[Product]), [Units Sold Total])
Units Sold Top 3 Product = IF([Units Sold Rank by Product] <= 3, [Units Sold Total])

Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
TytlerJaxon
Frequent Visitor

Re: Top and Bottom 3 Ranked Countries From Selected Country by GDP in Chart

Hi @Seth_C_Bauer,

 

Thanks for the very speedy reply. I have made 3 calculated columns to get to the "Units Sold Top 3 Products". I have then used this as my values in the chart but it only appears to give me the Top three countries by value from the whole list. I was hoping it would give me the top 3 relative to a specific country that is selected by a slicer (and including the selected country on the chart for comparitive purposes). i.e. if I selected the UK, which is ranked 8th in the list, it would only show the countries ranked 7th, 6th and 5th above the UK (and similar for bottom 3 values) So in total there would be 7 countries displayed on the chart with UK being in the middle. At the moment I am just getting the 3 Countries that are 1st, 2nd and 3rd displayed on the chart.

 

I may have done something incorrect in the formulae.

Moderator v-qiuyu-msft
Moderator

Re: Top and Bottom 3 Ranked Countries From Selected Country by GDP in Chart

Hi @TytlerJaxon,

 

In your scenario, you can create another table (Table2) contains the distinct Country value. Make sure there is no relation created between Table2 and the fact table. Create a measure in this Table2 to get selected value in slicer:

 

Select Value = IF(HASONEVALUE(Table2[Country]),VALUES('Table2'[Country]),BLANK())

 

Then create a calculated column in fact to rank GDP within each year group:

Rank = RANKX(FILTER('Table1','Table1'[Year]=EARLIER(Table1[Year])),'Table1'[GDP],,1)

 

Also create a measure in fact table:

 

Top/Bottom 3 ranked =
var curr=LOOKUPVALUE(Table1[Rank],'Table1'[Country],'Table2'[Select Value],'Table1'[Year],MAX('Table1'[Year]))
return
CALCULATE(SUM('Table1'[GDP]),FILTER('Table1','Table1'[Rank]<= curr+2 && 'Table1'[Rank]>=curr-2 && 'Table1'[Year]=MAX('Table1'[Year])))

 

Drag the Country column from Table2 to slicer, create a clustered column chart, select one value from the slicer.

 

p1.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors