Filter formula Top N values. Duplicates ranked underneath - how?

[Minilin]

Hiya,

I have a table of risks and have got them into a top 5 report which works fine as long as there aren't two risks with the same value. As this then causes a spill error as it tries to stick the two with the same value into the cells surrounding it.

I have used the following formulas (left to right) in the report:
=FILTER(Table1 [ Risk/Issue Description] ,Table1[Risk Total]=LARGE(Table1[Risk Total],B4))
=LARGE(Table1[Risk Total],B4)
=FILTER(Table1 [ Mitigation Strategy] ,Table1[Risk Total]=LARGE(Table1[Risk Total],B4))

What can I add to the above formula that would prevent this from happening?

Rank 1 and 2 could have the same risk total, I wouldn't want it going into the same cell.

 

[Fluff]

That would suggest that you don't have any rows that meet the criteria.

 

[Minilin]

That would suggest that you don't have any rows that meet the criteria.

I've put test risks in two open and two closed, none of them pull through unfortunately.

 

[Fluff]

The priority # column is empty, which is why you get the error.

 

[Minilin]

The priority # column is empty, which is why you get the error.

Ah, cos it was meant for Issues I see and not Risks. Brilliant so that now works for the Issues.

Do you have a formula I can use for the Risk report; original formula:
=index(sort(Table1[[Risk/Issue Description]:[Mitigation Strategy]],4,-1),SEQUENCE(5),{1,4,5})

Again taking out the closed ones.

 

[Fluff]

Try

=INDEX(SORT(FILTER(Table1[[Risk/Issue Description]:[Mitigation Strategy]],Table1[Status]="Open"),4,-1),SEQUENCE(5),{1,4,5})

 

[Minilin]

Try

=INDEX(SORT(FILTER(Table1[[Risk/Issue Description]:[Mitigation Strategy]],Table1[Status]="Open"),4,-1),SEQUENCE(5),{1,4,5})

Fab, thank you again

 

[Fluff]

You're welcome & thanks for the feedback.