Need column with Rank by Date and by Empid

[Akhil_1411]

Hi All,

I am trying to create a Rank(Column) by date and by empid with a metric "EO%". Below is the expected result i am looking for.

Date	Emp ID	EO %	Rank
1/1/2021	123	10%	1
1/2/2021	23	20%	2
1/3/2021	134	30%	3
1/4/2021	234	40%	4
1/5/2021	123	23%	3
1/6/2021	23	22%	2
1/7/2021	134	54%	4
1/8/2021	234	11%	1
1/9/2021	123	7%	3
1/10/2021	23	3%	2
1/11/2021	134	8%	4
1/12/2021	234	2%	1

Can someone help me with this.

Thanks,

Akhil.

[themistoklis]

@Akhil_1411 

Are you sure the rank you show in your message is based on Date and EMP_ID? Every row has a distinct date so in the rank there wouldn't be same values

Please try the following formula:

Sorting_Rank = 
	RANKX (
        FILTER(
			ALL(
				'Table'[Date], 'Table'[Emp_ID]
				),
            'Table'[Date] = MAX('Table'[Date]) &&
            'Table'[Date] = MAX('Table'[Date])
            ),
			[EO%], , ASC, Dense
			)

[Akhil_1411]

@themistoklis ,

Thanks for your response. I have made mistake😶 it will be on the one date there will be multiple employees. Corrected table as below.

Date	Emp ID	EO %	Rank
1/1/2021	123	10%	1
1/1/2021	23	20%	2
1/1/2021	134	30%	3
1/1/2021	234	40%	4
1/2/2021	123	23%	3
1/2/2021	23	22%	2
1/2/2021	134	54%	4
1/2/2021	234	11%	1
1/3/2021	123	7%	3
1/3/2021	23	3%	2
1/3/2021	134	8%	4
1/3/2021	234	2%	1

[themistoklis · Accepted solution]

Hello @Akhil_1411 

Can you please try this:

Sorting_Rank = 
	RANKX (
        FILTER(
			ALL(
				'Table'[Date]
				),
            'Table'[Date] = MAX('Table'[Date])
            ),
			[EO%], , ASC, Dense
			)

or this:

Sorting Rank = IF (
    HASONEVALUE ( 'Table'[Date] ), 
    RANKX ( ALL ( 'Table'[Date] ), [EO%], , ASC, Dense)
)