Forum Discussion

kaylastarr's avatar
kaylastarr
Advocate V
2 years ago
Solved

Help using RankX as a measure

Hello All,

 

I am struggling to create a measure to display the rank in a table. I have a table with the following columns

Promo Name, Start Date, Region, Total Quoted Sales. I am trying to display the rank by Promo Name and Start date based on the sum of total sales.

 

My current measure is:

RankM = RANKX(ALLselected('Promo Summary Table'),CALCULATE(sum('Promo Summary Table'[Total Quoted Sales]), ALLEXCEPT('Promo Summary Table','Promo Summary Table'[Promo Name)),,DESC,Dense)

This is close to correct but instead of calculating for each PromoName - Start Date pair it calculates for each promo name 

If I create a column key unique for each Promo Name and Start date then each rank is just one. Any help would be appreciated



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, kaylastarr 

    I create a table:

     

    You can use the following DAX expression:

    Rank Test =
ROWNUMBER (
    ALLSELECTED ( 'Table'[Promo Name], 'Table'[Start Date] ),
    ORDERBY ( 'Table'[Promo Name], DESC, 'Table'[Start Date], ASC )
)

     

    Here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

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

2 Replies