Using function Rank or Rankx for grouped rankings

[SpanishEnjoyer]

Hello,

I have two tables related to each other by ID. One table has information about dates when a equipment failed and how it did and the other table contains information about all equipments (id,category, room, building)  just like this:

Eq ID	Month year	Failure Mode
001	2-2024	-
002	3-2024	-
002	6-2024	-
004	8-2024	-
003	2-2024	-

Eq ID	Cathegory	Room	Building
001	Electrical	10	A
002	Electrical	11	A
003	Mechanical	10	A
004	Mechanical	12	B

I already created a metric that counts how many times an equipment appears in table Failures:

Count_Failures = CALCULATE(COUNTROWS('Table Failures')

Also I used the function RANK to know the rankings of each equitment

Ranking  = RANK(DENSE,FILTER(ALL('Table Equipments'),'Table Equipments'[ID Eq]<>BLANK()),ORDERBY([Count_Failures],DESC))

However, I need to know for example, the ranking of equipment per cathegory or per room like the table below , it doesn´t matter if  it´s neccesary to created two new metrics. 

Eq ID	Cathegory	Room	Total Failures	Rank per Cathegory	Rank per Room
001	Electrical	10	1	2	1
002	Electrical	11	2	1	1
003	Mechanical	10	1	1	1
004	Mechanical	12	1	1	1

NOTE: I would like to keep using the metric Count_failures because it can changes over time instead of a calculated column in the table Equipments wich will have an static value.

[rajendraongole1]

Hi @SpanishEnjoyer -Create a new measure that ranks the equipment based on the number of failures within its respective category

Rank_Per_Category =
RANKX(
ALLSELECTED('Table Equipments'[ID Eq]),
[Count_Failures],
,
DESC,
DENSE
)

create another measure for rank for each room as below:

Rank_Per_Room =
RANKX(
ALLSELECTED('Table Equipments'[ID Eq]),
[Count_Failures],
,
DESC,
DENSE
)

this way you can achieve, hope it works.

[SpanishEnjoyer]

Thanks for your answer,  but the new metrics works as the metric Ranking that is already created.  It doesn´t rank the equipment by cathegory or room.

[Anonymous]

Hi @SpanishEnjoyer ,

I don't quite understand your grouping logic, if the room ordering 001 and 003 should be the same, they should both return 2.

Please detail your needs and demonstrate the expected results.

Best regards,
Community Support Team_ Scott Chang