Applying Ranking to values within a Group

[DanieeelD]

Hi All, 

Apologies bit of new starter with Power Bi and looking to get stuck in with learning to analyse my mates mini league 🙂

Using data retrieved from the Fantasy Premier League API I'm looking to rank manager's on a week by week basis. To do this I want to look at two columns, Event ID which is the week (i.e. 1) and Total Points. Example of the table structure can be seen below including what I would expect rank to show. 

Manager	Event	Total Points	Rank
Manager A	Week 1	50	1
Manager B	Week 1	30	2
Manager C	Week 1	25	3
Manager A	Week 2	80	2
Manager B	Week 2	90	1
Manager C	Week 2	60	3

This rank would then be needed to create a visual that shows the change in rank over time as the season progresses. However, I'm struggling to find the examples of how this might be done. I've tried creating a column using 

Rank per Event = RANKX(ALL('Manager Basic Info'[Event]), [Total Points]) but this just returns rank 1. Is it possible to create the ranking I need in Power Bi? Or do I need to extract each week's data into seperate tables and rank from there?

Thanks in advance, 

Dan 

[Anonymous · Accepted solution]

Hi @DanieeelD ,

Please try below steps:

1. below is my test table

Table:

2. please create a measure with below dax formula:

RK =
VAR cur_event =
    SELECTEDVALUE ( 'Table'[Event] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Event] = cur_event )
RETURN
    RANKX ( tmp, CALCULATE ( MAX ( 'Table'[Total Points] ) ) )

3. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @DanieeelD ,

this measure:

Measure = 
IF( ISFILTERED('Table'[Manager] ) 
    ,RANKX(
        ALLSELECTED( 'Table'[Manager] )
        , calculate( sum( 'Table'[Total Points] ) )
    )
    , BLANK()
)

allows to create this table visual:

Hopefully, this provides what you are looking for.

Regards,
Tom