Forum Discussion

IHam's avatar
IHam
Helper III
7 years ago
Solved

Ranking vs Date

Hi, I am trying to plot a graph of an individual's rank vs time based on their cumulative points score. So for instance on day 1 someone is ranked 3rd with 100 points and then by day 3 they are 2nd w...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may check the Page2 in attached file.Add a calendar table to get the cumulative total.Add 0 to the blank cumulative total.Then rank it.

    cumulative_points = 
IF (
    MAX ( 'Calendar'[Dates] ) <= MAXX ( ALL ( 'Table' ), 'Table'[Date] ),
    CALCULATE (
        SUM ( 'Table'[Points] ) + 0,
        FILTER (
            ALLSELECTED ( 'Calendar'[Dates] ),
            'Calendar'[Dates] <= MAX ( 'Calendar'[Dates] )
        )
    )
)

    Rank = 
IF (
    MAX ( 'Calendar'[Dates] ) <= MAXX ( ALL ( 'Table' ), 'Table'[Date] ),
    RANKX ( ALL ( 'Table'[person] ), [cumulative_points],, DESC )
)

    Regards,

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi IHam 

 

You may try create two measures to get them.For example:

cumulative_points =
CALCULATE (
    SUM ( Table[Points] ),
    FILTER (
        ALL ( Table ),
        Table[Person] = MAX ( Table[Person] )
            && Table[Date] <= MAX ( Table[Date] )
    )
)

Rank =
RANKX (
    FILTER ( ALL ( Table ), Table[Date] = MAX ( Table[Date] ) ),
    [cumulative_points],
    ,
    DESC
)

Regards,

Cherie

  • IHam's avatar
    IHam
    Helper III
    7 years ago

    Thanks so much for your time Cherie, however, when I plot a line graph with date on the x axis and the Rank on the y axis, it appears as a flat line at 1. Any thoughts?

    thanks again

    Ian

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi IHam 

       

      If you need put rank on the y axis,you may create calculated columns like below:

      cumulative_points = 
CALCULATE (
    SUM ( Table1[Points] ),
    FILTER (
        ALL (Table1 ),
        Table1[Person] =EARLIER(  Table1[Person] )
            && Table1[Date] <= EARLIER( ( Table1[Date] )
    )
))
      Rank = 
RANKX (
    FILTER ( ALL ( Table1 ), Table1[Date] = EARLIER( Table1[Date] ) ),
    [cumulative_points],
    ,
    DESC
)

      Regards,

      Cherie

      • IHam's avatar
        IHam
        Helper III
        7 years ago

        I can get the cumulative total for an individual but the rankinng is still appearing as a 1 irrespective of the time or the person slicer. Any ideas? And many thanks for your time with this.