I’m guessing this is the setup you’re looking for:
[RowID]
Type: Number
Formula: RowId(thisRow)
[Rank]
Type: Number
Formula: Rank(RowID,thisTable.RowID,true)
[Rank in Category]
Type: Number
Formula: Filter(thisTable,Category=thisRow.Category).Rank.Find(thisRow.Rank)
I know you mentioned 5 rows for each category, but I wanted to make sure it worked after deleting rows and with different numbers of rows in each category.
If you change the order of the rows, this will number them top to bottom each time, so the [Rank in Category] of the row can change if you move it’s position.