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,

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

  • IHam's avatar
    IHam
    Helper III
    7 years ago

    Thanks for you ongoing help. All selected didn't seem to work - its probably a mistake at my end. Here is the link - i created the file to test the dynamic ranking over time so its not private.

    https://1drv.ms/u/s!ApKSjzEfrd1yhzoiFSSTNlLGzXur

     

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

      Hi IHam 

      I'm afraid i cannot understand your expected output.Below are the 4 persons' cumulative points by date.Could you explain how to rank it?On 1/1/2019,the lowest points is 37 by person 4,but there're no points for other persons.

      Regards,

      Cherie

      • IHam's avatar
        IHam
        Helper III
        7 years ago

        No problem. So on the start date they all have no points so are equally ranked. After say a week, person 1 has a cumluative total of 400, person 2 300 and so on. So at that date the y-axis would plot person 1 as a rank of 1. In the next week person 2 and 3 scored more points and now person 1 has a rank of 3. The graph would then have a rank of 1 for the first week and a rank of 3 for the second week (or whatever time period we have). The ranking of everyone is calculated every day for their cumlative points and plotted on the y-axis. Hopefully this is clearer - i have a attached a bad diagram to try to help. I want to eventually apply this to a much larger database to see how a person's rank changes over a time period so any big risers and fallers can be spotted and seen on the graph.

        Many thanks for your continuing help!