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 with 250 points etc. The end product needs to be able to graph the daily ranking of points over time by person.

many thanks

 

 

 

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,

14 Replies

  • 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