Forum Discussion

BhavaniK's avatar
BhavaniK
Frequent Visitor
9 months ago
Solved

Need help on Ranking within table.

Hi Team,   I'm looking for some help to achieve the logic. Right now I have table with these columns Event, Number, Location, sum of delay imapact. in the visual filter I have added, Number-> Top 5...
  • KNP's avatar
    9 months ago

    It really depends on what you're expecting as an output.

     

    Assumptions for the below:

    - I used your sample data and created a table called 'sample'

    - I created a measure called _delay = SUM('sample'[Delay])

     

    Create this measure, put it in the visual filters where it is 1.

     

    __rank = 
    VAR __locations =
        ALLSELECTED('sample')
    VAR __ranked =
        ADDCOLUMNS(
            __locations
            , "__delay", [_delay]
            , "__rank", RANKX(
                __locations
                , [_delay]
                , BLANK()
                , DESC
                , Skip
            )
        )
    VAR __top =
        TOPN(
            5
            , __ranked
            , [__rank], ASC
            , [__delay], DESC
            , 'sample'[Location], ASC
        )
    VAR __isTop =
        CONTAINS(
            __top
            , 'sample'[Location], SELECTEDVALUE('sample'[Location])
        )
    VAR __result =
        IF(
            __isTop
            , 1
            , 0
        )
    RETURN __result

     

     

    My code is GitHub Copilot assisted, but always tested.