Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a soltution in order to dynamically select rows of data whenever I select a certain country, I also want to show the next three highest and lowest countries on a line chart visual for comparison. Currently I can only show the country that is selected (see below)
I am trying to figure out the best solution, whether by a measure or different way of modelling the data, in order to achieve this (see below). I need this to work dynamically so that no matter which country is selected it always shows the next 3 higher and 3 lower countries.
See data sample below,
Rank | Country | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 |
1 | United States | 665946 | 21187714 | 21622974 | 21944609 | 22434273 | 23122972 | 23592259 | 23991574 | 24381019 | 24808095 |
2 | China | 141492 | 10563035 | 11312652 | 12071091 | 12875031 | 13730088 | 14572214 | 15464295 | 16407447 | 17387765 |
3 | Japan | 48095 | 4797509 | 4855343 | 4903247 | 4995473 | 5040480 | 5071698 | 5091778 | 5144375 | 5195939 |
4 | Germany | 41807 | 3784928 | 3845777 | 3912391 | 4020429 | 4123279 | 4209593 | 4268220 | 4333461 | 4390587 |
5 | India | 47106 | 2440762 | 2629894 | 2830528 | 3012621 | 3220392 | 3451175 | 3708248 | 3981413 | 4265247 |
6 | United Kingdom | 58207 | 2805185 | 2864325 | 2911206 | 2953920 | 2991449 | 3028688 | 3075484 | 3133607 | 3189651 |
7 | France | 53058 | 2774191 | 2795434 | 2827496 | 2876037 | 2928581 | 2978788 | 3024748 | 3070761 | 3117208 |
8 | Brazil | 27134 | 2251820 | 2174286 | 2091943 | 2120361 | 2167904 | 2235083 | 2316515 | 2389504 | 2463866 |
9 | Italy | 27562 | 2046799 | 2061364 | 2083273 | 2115729 | 2144191 | 2166844 | 2187854 | 2211153 | 2233969 |
10 | Korea, South | 35341 | 1659389 | 1705734 | 1751477 | 1803134 | 1859555 | 1910936 | 1961347 | 2010347 | 2057486 |
Any help will be very much appreciated!
Thanks,
Mark.
Solved! Go to Solution.
Hey Mark,
the pbix file attached to this post (I'm sorry for the confusion, but maybe I will stick to onedrive links instead of fileuploads because they do not update automatically :-)) now contains another unrelated table (Years unrelated).
Basically the change was not that big, instead of using the SUM OF ALL YEARS, now I use the AMOUNT OF THE SELECTED YEAR to determine the ranking.
There are two new measures:
I adjusted the measure "rank Countries", it now uses the measure "SUM Amount by Selected Year", I also added the "old" measure" this is now called "rank Countrires All Years" for comparison reasons, but is seems that the ranking for all years equals the ranking for one paricular year, maybe my calculation is wrong, or it's the data 🙂
Please provide Feedback if my adjustments provide the solution you require, if not, can you please provide feedback, how to check this, based on the data you provided.
Regards,
Tom
Hey,
maybe the attached pbix file provides what you are looking for.
Please be aware that I unpivoted your sample data, this means I transformed from wide (many columns) to long (many rows)
As @Greg_Deckler already mentioned an unrelated table is necessary to select the country.
Then then there are some measures
This measure looks looks like this
map Amount = var countriesAboveAndBelow = UNION( FILTER( FIRSTNONBLANK('Table1'[Country],1) ,'Table1'[Country] = [Selected Country] ) , UNION( SUMMARIZE( FILTER( ADDCOLUMNS( GROUPBY( 'Table1' ,'Table1'[Country] ) ,"theRank", [rank Countries] ) ,[theRank] > [RANK of selected Country] && [theRank] <= [RANK of selected Country] + 3 ) ,Table1[Country] ) ,SUMMARIZE( FILTER( ADDCOLUMNS( GROUPBY( 'Table1' ,'Table1'[Country] ) ,"theRank", [rank Countries] ) ,[theRank] < [RANK of selected Country] && [theRank] >= [RANK of selected Country] - 3 ) ,Table1[Country] ) ) ) return CALCULATE( SUM(Table1[Amount]) ,countriesAboveAndBelow )
Hopefully this is what you are looking for
Regards
Tom
Hi @TomMartens,
Thanks for your swift reply, this works perfectly in terms of filtering and selecting the right sets of countries, also the data was originally unpivoted in the same way you have it which makes it easier, I pivoted it thinking that might be the best way.
One detail I forgot to include however (apologies) was that the ranking of the countries needs to be based on a given year, in this case the current year 2018 rather than a ranking based on the SUM across all years. I previously made a step in the query to sort largest to smallest on the '2018' column in the pivoted sample data to achieve this. Do you know of a way to achieve this based on the solution you have provided?
I'm hoping it's only a minor tweak to what you have already provided which is brilliant btw!
Many Thanks,
Mark.
Hey Mark,
the pbix file attached to this post (I'm sorry for the confusion, but maybe I will stick to onedrive links instead of fileuploads because they do not update automatically :-)) now contains another unrelated table (Years unrelated).
Basically the change was not that big, instead of using the SUM OF ALL YEARS, now I use the AMOUNT OF THE SELECTED YEAR to determine the ranking.
There are two new measures:
I adjusted the measure "rank Countries", it now uses the measure "SUM Amount by Selected Year", I also added the "old" measure" this is now called "rank Countrires All Years" for comparison reasons, but is seems that the ranking for all years equals the ranking for one paricular year, maybe my calculation is wrong, or it's the data 🙂
Please provide Feedback if my adjustments provide the solution you require, if not, can you please provide feedback, how to check this, based on the data you provided.
Regards,
Tom
Hey @TomMartens,
Apologies, I may be being blind, did you mean to attach the file to your latest reply? I tried downloading the file you sent in the previous post but that doesn't have the changes you have just made for the year selection.
Regards,
Mark.
Hey Mark,
you are not blind 🙂 please excuse.
The newer version is now attached to my 2nd answer 🙂
Cheers,
Tom
Hey Tom,
You sir are a genius! above and beyond what I was looking for! I thought you may have only set '2018' as the ranking year but I also now have the capability of switching the year for ranking quickly and easily. I've been struggling with that solution for months, clearly I have a lot more to learn with DAX.
Many Thanks,
Mark.
You are welcome!
It has been a pleasure!
Regards,
Tom
P.S.:
In the next days, I will create a blog post, where I will describe not just this solution but also some kind of "DAX" thinking 🙂
Need sample data to work with. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said, you will need to have a disconnected country table and then use a measure. The measure will likely need a SUMMARIZE and then ADDCOLUMNS to add a RANKX column to rank. Then you will need something like EARLIER. Overall, a fairly complex calculation but definitely doable. But, sample data is key. (that can be copied and pasted)
Hi Greg,
Thanks for the posting tips and the quick reply , I have now edited to include a sample of the data I'm working with.
Regards,
Mark.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |