Dax to define Rank based on 2 conditions

[sizi]

Hello All,

I have a requirment where i need to rank the employee based on Total tasks and min days taken. 

 Below is the data :

Employee	Total Task	Min Days Taken
John	32	20
Bethany	20	32
Johnny	25	20
Claire	30	15

The result is as below :

Employee	Total Task	Min Days Taken	Rank
John	32	20	2
Bethany	20	32	4
Johnny	25	20	3
Claire	30	15	1

The rank should be based on below conditions:

1. Check for Highest value under total task column

2.Check for least/min number of days taken under Min Days Taken column

The ratio task :days taken needs to be considered and not average.

Based on these condition rank shall be defined as above. If condition is true 1st rank and so on for all records in the table giving rank in sequence based on the condition defined.

Kindly help. 

Thanks,

[ThxAlot]

.

[sizi]

second dax giving error wheeras ratio rank dax ranking lowest as 1 and highest as 4

[sizi]

its giving me lowest employee has highest rank . 

[Bibiano_Geraldo]

Why John is first and Claire is second? Because the average show me Claire in the first position

[sizi]

Thanks for the highlighting it. It was by mistake. The result should be below : 

Employee	Total Task	Min Days Taken	Rank
John	32	20	2
Bethany	20	32	4
Johnny	25	20	3
Claire	30	15	1

However we wont consider average but ratio  task : min days taken. 

[Bibiano_Geraldo]

Try this:

1.  Create a nem measure with the following code:
   Average = AVERAGEX(Data,DIVIDE(Data[Min Days Taken],Data[Total Task]))
2. Create other measure with the following code:
   Rank = RANKX(ALLSELECTED(Data[Employee]),[Average],,ASC)
3. Create a Matrix table and use employee for rows and the Rank measure for values

Note: Replace 'Data' with your table name

[sizi]

Its giving me same average for all the employees as 2.

Actual data is as below  and rank should be defined accordingly.

Employee	Total Task	Min Days Taken	Rank
John	32	20	1
Bethany	20	32	5
Johnny	7	20	7
Claire	24	35	4
Tiffany	24	32	3
Bobby	17	58	6
Jay	28	20	2

[Fowmy · Accepted solution]

@sizi 

Try this measure:

Rank Emp = 
IF(
    ISINSCOPE( Table29[Employee] ),
    RANKX( 
        ALLSELECTED( Table29[Employee] ) ,
        DIVIDE(
            CALCULATE( SUM( Table29[Total Task] ) ),
            CALCULATE( SUM( Table29[Min Days Taken] ) )
        )
    )
)

[sizi]

Perfect. It worked. Thanks so much.👍

[Fowmy]

@sizi 

Glad it works!
Please accept my answer as the solution so it will be beneficial for others with the same questions 

[Bibiano_Geraldo]

something you did wrong, show me your measures to make sure they a correct. i just updated the table as you can see bellow

[sizi]

Hi, I have another scenario where in i need to get the average of 2 rank column and define the overall rank with low value as highest.

Below is the data:

Employee	DemandRank	SLARank	BA Rank
claire	4	2	3
Chris	4	4	4
Rock	2	6	4
Bella	6	3	4.5
Harry	7	1	4
Stefanie	1	5	3

The result shall be :

Employee	DemandRank	SLARank	Avg Demand+SLA	Overall Rank
John	2	3	2.5	1
claire	4	2	3	2
Chris	4	4	4	3
Rock	2	6	4	3
Bella	6	3	4.5	4
Harry	7	1	4	3
Stefanie	1	5	3	2

AverageRank = RANKX( ALL('Table'[Employee]), [BA Rank], , ASC ) ,,

i tried multiple dax but this was close however it still skips the sequence of ranking 1-10.

Kindly help.

Thanks,

[sizi]

It worked. I did something wrong. However i want to exclude employee 134 from the table and ranking should be defined accordingly.

Currently the result is below :

Employee	Total Task	Min Days Taken	Rank
John	32	20	2
Bethany	20	32	6
Johnny	7	20	8
Claire	24	35	5
Tiffany	24	32	4
Bobby	17	58	7
Jay	28	20	3
134	2		1

Could you help to filter out the employee and update the ranking accordingly?

Thanks. Appreciate your help. 👍