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

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