cancel
Showing results for
Did you mean:
Highlighted
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
Community Support

## 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.

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.
3 REPLIES 3
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
Frequent Visitor

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

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.

Community Support

## 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.

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.

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors