Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

@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

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.