Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
Best Regards,
Qiuyun Yu
@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])
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.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |