Ranking calculation

Hi SeGr ,

Here’s a dynamic approach you can use with DAX:

Resource Rank in SubCategory = 
VAR RankingTable =
    SUMMARIZECOLUMNS(
        Table[Resource],
        Table[SubCategory],
        "@Ranking", RANKX(
            ALLSELECTED(Table[Resource]),
            [Total Cost], , DESC
        )
    )
RETURN
    MINX(
        FILTER(
            RankingTable,
            RankingTable[SubCategory] = SELECTEDVALUE(Table[SubCategory])
        ),
        [@Ranking]
    )

This measure creates a virtual table of every Resource/Sub-Category combination and calculates the rank for each resource based on [Total Cost]. Then, it returns the rank for the relevant Sub-Category in your visual, even when Resource isn’t included.  If you want to show the top or average resource rank within each Sub-Category, you can swap out MINX for MAXX or AVERAGEX as needed. Make sure your [Total Cost] measure works at the correct granularity and your relationships are set up properly. If you have ties, MINX just gives the smallest rank (i.e., top performer).