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.

       

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

        Hi IHam 

         

        You may change the ALL function in the formula to ALLSELECTED funtion.If it is not your case,please share the sample file.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

         

        Regards,

        Cherie