Ranking By Month and Score Company Wide and in Groups

[jharsh]

I have been looking and not found an answer that suits my needs.  I have a request to create multiple visuals for Employees ranked by a score company wide, with in groups, and month over month individually.

The date would look like this

ID  | Score | Date     | Group

---------------------------

1   |    95   | 9/1/21  |  A

2   |    94   | 9/1/21  |  A

3   |    85   | 9/1/21  |  B

4   |    90   | 9/1/21  |  B

1   |    90   | 8/1/21  |  A

2   |    80   | 8/1/21  |  A

3   |    90   | 8/1/21  |  B

4   |    70   | 8/1/21  |  B

.

.

.

Expected Results would be a 2 tables and a line chart

Table 1 Expected Results

For Max Date

ID  | Score | Date     | Group  | Rank

--------------------------------------

1   |    95   | 9/1/21  |  A         |   1

2   |    94   | 9/1/21  |  A         |   2

3   |    85   | 9/1/21  |  B         |   4

4   |    90   | 9/1/21  |  B         |   3

Table 2 Expected Results

ID  | Score | Date     | Group | Rank

-------------------------------------

1   |    95   | 9/1/21  |  A        |   1

2   |    94   | 9/1/21  |  A        |   2

3   |    85   | 9/1/21  |  B        |   2

4   |    90   | 9/1/21  |  B        |   1

1   |    90   | 8/1/21  |  A        |   1

2   |    80   | 8/1/21  |  A        |   2

3   |    90   | 8/1/21  |  B        |   1

4   |    70   | 8/1/21  |  B        |   2

Line Chart showing for ID 1 Month 8/1/21 Value 90 then Month 9/1/21 Value 95 and so on. 

I have been tryin to get RANKX to work to no avail.  I have tried doing RANKX(Table1,Table1[Score],,DENSE) and RANKX(Table1,CALCULATE(SUM(Table1[Score])),,DENSE)  but I don't need to sum my scores at a group or individual level because I want to look at them month over month and at a company level it should only be the most recent month used for the ranking.  

Thanks for any help or guidance on this.

[AlexisOlson · Accepted solution]

RANKX can be hard. I almost never get it right the first time.

You can get the rank within a month by writing

RANKX ( ALLEXCEPT ( Table1, Table1[Date] ), CALCULATE ( MAX ( Table1[Score] ) ) )

 and the rank within the group and month like this:

RANKX (
    ALLEXCEPT ( Table1, Table1[Group], Table1[Date] ),
    CALCULATE ( MAX ( Table1[Score] ) )
)

For Table 1, we can add a check to see if the current date matches the maximal date to return a blank for all prior months.