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

The real table I am dealing with has some days when people register points and some day when they don't. Would creating a date table and then creating a rank score for each person per day (irrespective if they had accumulated any points on that day) be a way forward? Each person has a different rank (by cumulative total) on each day whether their cumulative totals have changed or not and this is what i would like to plot on the y-axis.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I dont understand you at all. Basically you need to remove all the registers in your table when a numeric field is equals to 0, do you?

 

Because I think I can help with that.

  • IHam's avatar
    IHam
    Helper III
    7 years ago

    Not really. Imagine a football league table. Teams accumulate points at different times throughout the year. I need to plot the rank of a team (its league position) by time as it progresses through a season.