Forum Discussion

BhavaniK's avatar
BhavaniK
Frequent Visitor
10 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-> sum of delay.  here is the actual problem arise, if there is multiple rows have same delay then in the table it shows more than Top 5. is anyone can help to resolve this issue within power bi? Sample data:

EventNomberLocationDelay
 1Hyd11
 2chennai3
 3madurai2
 4nagarcoil2
 5salem2
 6rmd2
 7trichy1
 8ngt1
Need Help
  • KNP's avatar
    KNP
    10 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.

     

     

5 Replies

  • KNP's avatar
    KNP
    Super User
    10 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.

     

     

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    10 months ago

    Rank by Delay =
    RANKX(
    ALL('YourTable'[Number]),
    CALCULATE(SUM('YourTable'[Delay])),
    , DESC, DENSE
    )

    • v-menakakota's avatar
      v-menakakota
      Community Support
      10 months ago

      Hi BhavaniK ,

      Thanks for reaching out to the Microsoft fabric community forum. 

       

      I would also take a moment to thank KNP   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

       

      Best Regards, 
      Community Support Team  

      • v-menakakota's avatar
        v-menakakota
        Community Support
        9 months ago

        Hi BhavaniK ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.
         

        Regards,
        Microsoft Fabric Community Support Team.