Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jharsh
Frequent Visitor

Ranking By Month and Score Company Wide and in Groups

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.

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1633041821786.png

 

View solution in original post

1 REPLY 1
AlexisOlson
Super User
Super User

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.

AlexisOlson_0-1633041821786.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.