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