Forum Discussion

oldnapkin's avatar
oldnapkin
Regular Visitor
9 years ago
Solved

How to RANKX within a Date

I want to numerically rank an amount within a specific date. Say my data is this.   Person Sales Date Bob 10 January Jeff 20 January Steve 30 January Bob 50 February Jeff 40 February Steve 30 Febru...
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    oldnapkin

     

    In this scenario, I assume you should also have a year column in your column.

     

    Then you just need to create two measures:

     

    RankWithinYear = RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Sales])))
    RankWithinMonth = RANKX(ALLEXCEPT('Table','Table'[Month],'Table'[Year]),CALCULATE(SUM('Table'[Sales])))

     

     

    If you don't have that Year column in your source table, your measures will be like below:

     

    RankWithinMonth = RANKX(ALLEXCEPT('Table','Table'[Month]),CALCULATE(SUM('Table'[Sales])))
    Rank over entire table = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])))

    Regards,

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

oldnapkin

 

In this scenario, I assume you should also have a year column in your column.

 

Then you just need to create two measures:

 

RankWithinYear = RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Sales])))
RankWithinMonth = RANKX(ALLEXCEPT('Table','Table'[Month],'Table'[Year]),CALCULATE(SUM('Table'[Sales])))

 

 

If you don't have that Year column in your source table, your measures will be like below:

 

RankWithinMonth = RANKX(ALLEXCEPT('Table','Table'[Month]),CALCULATE(SUM('Table'[Sales])))
Rank over entire table = RANKX(ALL('Table'),CALCULATE(SUM('Table'[Sales])))

Regards,