How to create ranking using Matrix table and multi select months

[JaynaC]

Hi Power BI Community,

I have a question regarding how to create a dynamic ranking using a matrix table that allows multi select month year.
At the moment the ranking works for single select month year but when I select additional month year the ranking doesn't work as it should.

I'm currently using the following

Rank =

RANKX (
FILTER ( ALLSELECTED ( 'CrossPurchase_Category' ), 'CrossPurchase_Category'[Category] = MAX ( 'CrossPurchase_Category'[Category] ) ),
CALCULATE ( SUM ( 'CrossPurchase_Category'[baskets] ) ),)

which gives me this when only Nov 23 is selected

But if I select Dec23 as well then the rank is no longer 1-14

Any idea how to solve this?

[Anonymous · Accepted solution]

Thanks for the reply from @amitchandak , please allow me to provide another insight:

Hi @JaynaC ,

Maybe you can try formula like below, when i choose muti month in slicer, it works well.

Total Baskets = SUM(CrossPurchase_Category[Baskets])

Dynamic Rank =
RANKX ( ALLSELECTED ( slicer_[Category] ), [Total Baskets],, ASC, DENSE )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JaynaC]

@amitchandak 
Thanks for that... It's almost there I used the M1 and new rank measures however the ranking is going horizontally instead of vertically.. do you know how I could change that?

[amitchandak]

@JaynaC , Try like

Rank =

RANKX (
FILTER ( ALLSELECTED ( 'CrossPurchase_Category'[Category] ), 'CrossPurchase_Category'[Category] = MAX ( 'CrossPurchase_Category'[Category] ) ),
CALCULATE ( SUM ( 'CrossPurchase_Category'[baskets] ) ))

Or create a measure

M1= CALCULATE ( SUM ( 'CrossPurchase_Category'[baskets] ), FILTER ( ALLSELECTED ( 'CrossPurchase_Category'[Category] ), 'CrossPurchase_Category'[Category] = MAX ( 'CrossPurchase_Category'[Category] ) ) )

Try new Rank Function

Rank(dense, ALLSELECTED ( 'CrossPurchase_Category'[Category] ) orderby(M1,desc))