Dynamic ranking

[gaznez]

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:  

RankVolOM = RANKX(ALL(Dates[Date]), [TotalVol])

My result 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!!

[gaznez · Accepted solution]

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)

)

RETURN

RANKX(

    AllSelectedDates,

    [TotalVolOM],

    ,

    DESC,

    Dense)

[PabloVallejo12]

Try to create a normal measure without rankx, and add a filter on the visual whit that requirement , like that :

[gaznez]

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'.