Forum Discussion
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
- Selected Year
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:
- 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
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.