cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IHam
Helper III
Helper III

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

 

pbirank.png

 

 

1 ACCEPTED SOLUTION

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

14 REPLIES 14
v-cherch-msft
Microsoft
Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

ranking test.png

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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.

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!20190321_100951.jpg

Anonymous
Not applicable

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.Smiley Wink

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

That does the trick - many thanks for your help and patience with this!

I am much obilged

 

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
Not applicable

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.