Need help on Ranking within table.

[BhavaniK]

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:

Event	Nomber	Location	Delay
	1	Hyd	11
	2	chennai	3
	3	madurai	2
	4	nagarcoil	2
	5	salem	2
	6	rmd	2
	7	trichy	1
	8	ngt	1

[Ashish_Mathur]

Hi,

Clearly show the result that you are expecting.

[v-menakakota]

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]

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.

[Shahid12523]

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

[KNP · Accepted solution]

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.