Forum Discussion

SeGr's avatar
SeGr
Helper 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 (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!

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

4 Replies

  • SeGr's avatar
    SeGr
    Helper I
    1 year ago

    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]
        )
  • rohit1991's avatar
    rohit1991
    Super 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
      Helper 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
      Helper 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]
      )