cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Community Support
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.

 

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

Community Support
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.

 

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors