Forum Discussion

JaynaC's avatar
JaynaC
New Member
2 years ago
Solved

How to create ranking using Matrix table and multi select months

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?

 

 







Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

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

  • JaynaC's avatar
    JaynaC
    New Member
    2 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.