Forum Discussion
Ranking vs Date
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,
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!
I didn´t see the whole problem that you are talking, but looking your table, you only have one register by date, meaning that only one person scores by day, so If you do a Line Graph you will never have two points on the same column, and you have another problem, you have in all of you registers the Rank equals to 1, so if you see a Linear Graph you will only see a Row. If you want to now the registers by person, You have to create a DAX that counts the registers grouping by person. I swear this can help you a bit.:smileywink:
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.
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.
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.
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,
That does the trick - many thanks for your help and patience with this!
I am much obilged