RANKX based on two numerical columns and grouped

[logrt]

Hi there, hope you can help me with this:

I have the following table, with a set of equipments that belongs to different fleets:

Equip	Stops	Hours	Fleet
A	4	6	X
B	3	5	X
C	2	4	X
D	4	3	X
E	3	1	Y
F	4	2	Y
G	4	5	Y
H	3	6	Z
I	2	2	Z
J	4	3	Z

I want to rank these equipments first by stops and then by hours, according to each fleet, like this:

Equip	Stops	Hours	Fleet	RANK
A	4	6	X	1
B	3	5	X	3
C	2	4	X	4
D	4	3	X	2
E	3	1	Y	3
F	4	2	Y	2
G	4	5	Y	1
H	3	6	Z	2
I	2	2	Z	3
J	4	3	Z	1

Also i want a measure that returns me the equip that ranks 2nd in every fleet, is that possible?

I am still learning with Power Bi and DAX functions

Regards

[MFelix · Accepted solution]

Hello @logrt

Create the following classification measure:

Rank = 
RANKX (
    FILTER (
        ALLSELECTED ( 'Table'[Fleet]; 'Table'[Equip]; 'Table'[Hours]; 'Table'[Stops] );
        'Table'[Fleet] = SELECTEDVALUE ( 'Table'[Fleet] )
    );
    CALCULATE ( SUM ( 'Table'[Stops] ) + SUM ( 'Table'[Hours] ) / 10000 )
)

By having the Total Hours divided by 10000 to make a small decimal that changes the order if only d.o the sum of stop and hours has to be you would not get the tiebreaker.

For second classification you can use the above measurement and filter the display by classification 2