Forum Discussion
Dynamic ranking
Hi all,
I have a matrix with dates as the rows, branch as the columns, and then sales volumes and a rank as the values.
The measure for my rank is:
I have a Date slicer - and if I select 2023, the data retains it's original rank (presumably because I used ALL(Dates[Date]). How can I get the ranking to update so rank 1 becomes the highest value in the dates I've selected? (In the example above - Jun 23 would become Rank 1, August 23 Rank 2 etc.)
Thanks for any help!!
UPDATE: I managed to figure this out if anyone is interested (with the help of ChatGPT)
The following formula works (I'm not sure why/how)RankVolOM = VAR AllSelectedDates = CALCULATETABLE(VALUES(Dates[Date]),ALLSELECTED(Dates))RETURNRANKX(AllSelectedDates,[TotalVolOM],,DESC,Dense)
3 Replies
Try to create a normal measure without rankx, and add a filter on the visual whit that requirement , like that :
Hi PabloVallejo12 , thanks for the suggestion. However - I don't want to show the top N values. I may want to select 2 years and see the ranking for all volumes in that date range.
Also when I click on filters - my only options are things like 'is less than', 'is greater than', 'is not' etc. There are no options for 'Top N'.
UPDATE: I managed to figure this out if anyone is interested (with the help of ChatGPT)
The following formula works (I'm not sure why/how)RankVolOM = VAR AllSelectedDates = CALCULATETABLE(VALUES(Dates[Date]),ALLSELECTED(Dates))RETURNRANKX(AllSelectedDates,[TotalVolOM],,DESC,Dense)