DAX to define Overall rank based on 2different Ranks

[sizi]

Hello All,

I need to define rank for each employee based on average of 2 defined ranks.

Below is the original data:

Employee	TaskRank	SLA	Avg(Task+SLA)
John	2	3	2.5
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 overall rank shall be based on Avg values:

Employee	TaskRank	SLA	Avg(Task+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

Kindly help. Thanks in advance.

[v-tianyich-msft]

Hi @sizi ,

Try the following expression:

Rank = var _t = ADDCOLUMNS('Table',"Rank",RANKX(ALL('Table'),[AVG],,ASC,Dense))
RETURN MAXX(_t,[Rank])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[sizi]

Below is the data detaile data and how  the calculation is done. Task rank is defined based on total task ; Avg sla is sla complaint task against task qulaified for SLA out of Total task and the result is defined rank and then task rank + sla rank gives the overall avg which again is defined to get the final rank.

Employee	Total Task	TaskRank	Task Qualified for SLA	SLA Complaint Task out of Qualified	Avg of sla complaint	Avg SLA rank	Total Rank Avg (TaskRank+AvgSLARank	Final Rank
John	27	1	7	3	42.85714286	3	2	2
claire	16	4	5	2	40	4	4	4
Chris	22	2	5	4	80	1	1.5	1
Rock	14	5	6	3	50	2	3.5	5
Bella	11	6	6	3	50	2	4	6
Harry	5	7	3	1	33.33333333	5	6	7
Stefanie	19	3	4	2	50	2	2.5	3

[sizi]

It isnt working for me. Task rank, sla rank is all measure defined from measures. I dont know the dax doesnt work for me 😞

[v-tianyich-msft]

Hi @sizi ,

If you want to use measure, try it:

Measure = var _t = ADDCOLUMNS('Table',"Rank",RANKX(ALL('Table'),'Table'[Avg(Task+SLA)],,ASC,Dense))
RETURN MAXX(_t,[Rank])

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[sizi]

all the 3 columns are measures defined fyi.

[sizi]

It showing 1 for all the employees.

[Fowmy]

@sizi 

Use this measure:

Rank BA Avg = 
IF(
    ISINSCOPE( Table35[Employee]),
    RANKX( 
        ADDCOLUMNS( ALL(Table35) , "Avg" , [BA Avg] ),
        [BA Avg],,
        ASC,
        Dense
    )
)

[Fowmy]

@sizi 

Create a caluclated Column:

Rank = RANKX( SUMMARIZE(ALL(Table),Table[Employee],Table[Avg(Task+SLA)]) ,Table[Avg(Task+SLA)] ,,ASC,Dense)

[sizi]

Avg column is a measure. The above dax is giving error because of that.