cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TytlerJaxon Frequent Visitor
Frequent Visitor

Measure to include current selection and rows above and below dynamically

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)

Current.png 

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.

 Aim.png

See data sample below,

 

RankCountry2013201420152016201720182019202020212022
1United States665946211877142162297421944609224342732312297223592259239915742438101924808095
2China141492105630351131265212071091128750311373008814572214154642951640744717387765
3Japan48095479750948553434903247499547350404805071698509177851443755195939
4Germany41807378492838457773912391402042941232794209593426822043334614390587
5India47106244076226298942830528301262132203923451175370824839814134265247
6United Kingdom58207280518528643252911206295392029914493028688307548431336073189651
7France53058277419127954342827496287603729285812978788302474830707613117208
8Brazil27134225182021742862091943212036121679042235083231651523895042463866
9Italy27562204679920613642083273211572921441912166844218785422111532233969
10Korea, South35341165938917057341751477180313418595551910936196134720103472057486

 

Any help will be very much appreciated!

Thanks,

Mark.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

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:

  • Selected Year
    Just contains the value of the selected year

  • SUM Amount by Selected Year
    Calculates the Amount for the selected year

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 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
9 REPLIES 9
Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

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)


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


TytlerJaxon Frequent Visitor
Frequent Visitor

Re: Measure to include current selection and rows above and below dynamically

Hi Greg,

 

Thanks for the posting tips and the quick reply Smiley Happy,  I have now edited to include a sample of the data I'm working with.

 

Regards,

Mark.

Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

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

  • SELECTED Country (can be hidden)
    this measure returns the selected country from the unrelated table
  • SUM Amount across ALL Years (can be hidden)
    this measure sums the value across all the years, this measure is the basis for the ranking
  • rank Countries (can be hidden)
    this measure ranks the measure [SUM Amount across ALL Years] for all the countries
  • RANK of selected Country (can be hidden)
    this measure returns the RANK for the selected country and is used in the final measure to filter the counries (below and above accordingly)
  • map Amount
    this is the final Measure, basicall this measure works like this
    • create a table that contains the selected country from the unrelated table / slicer,  the three countries that are above (a greater rank), and the three countries that are below (a smaller rank), 
    • use this table to filter the countries

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

 

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
TytlerJaxon Frequent Visitor
Frequent Visitor

Re: Measure to include current selection and rows above and below dynamically

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.

Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

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:

  • Selected Year
    Just contains the value of the selected year

  • SUM Amount by Selected Year
    Calculates the Amount for the selected year

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 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
TytlerJaxon Frequent Visitor
Frequent Visitor

Re: Measure to include current selection and rows above and below dynamically

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.

Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

Hey Mark,

 

you are not blind :-) please excuse.

The newer version is now attached to my 2nd answer :-)

 

Cheers,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
TytlerJaxon Frequent Visitor
Frequent Visitor

Re: Measure to include current selection and rows above and below dynamically

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.

Super User
Super User

Re: Measure to include current selection and rows above and below dynamically

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 :-)

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!