Ranking calculation

[SeGr]

Hi,

Here's what I'm working on:

I have a hierarchy made up of Sub-Category and Resource, and both of them come from the same table.

I’ve already calculated the rank based on cost for each level (Sub-Category and Resource) separately, and that part is working fine.

Now, I want to show a matrix or table that displays:

- The Sub-Category

- The rank of that Sub-Category based on cost (which is working correctly)

- And also the rank of the individual Resources, but without adding Resource in the visual

So essentially, I want to include the Resource-level rank in the table, even though I’m only displaying Sub-Categories.

Any ideas on how to do that?

Total Cost is just a sum of Cost Column.

Sub-Category Rank Measure = 

RANKX(
    ALLSELECTED('Table'[SubCategory]),
    [Total Cost],,DESC
)

Resource Rank Measure = 

RANKX(
    ALLSELECTED(
        'Table'[Resource]),
        [Total Cost],
        ,DESC
)

Thank you!

[rohit1991]

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).

[SeGr · Accepted solution]

I fixed the calculation:

Here is the Code:

Ranking 2 =

var Table1 =

SUMMARIZECOLUMNS(

    'Table'[Resouce],

    'Table'[SubCategory],

    "@Ranking",

    CALCULATE(

        RANKX(

            ALLSELECTED('Table'[Resource]),

            [Total Cost],,DESC

        ),

        allselected('Table'[SubCategory])

    )

)

return

minx(

    Table1,

    [@Ranking]

)

[SeGr]

Hi Rohit,

Thanks for your reply.

The code doesn't work.

1. The FILTER on ALL (Resouce) with the reference on Sub-Category. I can only filter by Resource column, as it is the only column from that virtual table

I changed the FILTER to ALL(Table) instead of ALL(Table[Resouce]) to make the code work and it returns a 1 on all rows.

Below there's a calculated table that works and produces the right result. Even by covering the calc table in an interator (minx, averagex etc) still doesn't work and returns 1 on all rows.. 

I know that not having the right granularity inside the table/matrix makes this very tricky....if you have any other ideas, they would be greatly appreciated!

[SeGr]

I've managed to create a calculated table that returns the result correctly, but I haven't been able to transform it in a measure:

    SUMMARIZE(

        ADDCOLUMNS(

            SUMMARIZE(

                'Table',

                'Table'[Resource],

                'Table'[SubCategory]

            ),

            "@Ranking", CALCULATE(

                RANKX(

                    SUMMARIZE(

                        ALLSELECTED('Table'),

                        'Table'[Resource],

                        'Table'[SubCategory]

                    ),

                    [Total Cost],

                    ,

                    DESC

                ),

                ALLEXCEPT(

                    'Table',

                    'Table'[Resource]

                )

            )

        ),

        'Table'[SubCategory],

        [@Ranking]

    )