Forum Discussion

SeGr's avatar
SeGr
Icon for Helper I rankHelper I
1 year ago
Solved

Ranking calculation

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...
Need Help
  • SeGr's avatar
    SeGr
    1 year ago

    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]
    )
rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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's avatar
    SeGr
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
    SeGr
    Icon for Helper I rankHelper I
    1 year ago

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