Help using RankX as a measure

[kaylastarr]

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

[Anonymous · Accepted solution]

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.

[lbendlin]

Obligatory SQLBI link:  https://www.sqlbi.com/articles/use-of-rankx-in-power-bi-measures/