How to calculate rank in a collection

[kubalaml]

If I have a collection of Activites like this:

{Name: User A, Count: 100}

{Name: User B, Count: 100}

{Name: User C, Count: 75}

{Name: User D, Count: 50}

How could I calculate what rank each user is based on the Count field? I can Sort(Activities, Count, Descending), but that would put User A above User B even though they both have the same Count. I'm looking for an output like this. 

Rank	User	Count
1	User A	100
1	User B	100
3	User C	75
4	User D	50

Thanks!

[WarrenBelz]

Hi @kubalaml ,

Try

SortByColumns(
   Activities,
   "Count", 
   Descending,
   "User",
   Ascending
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[kubalaml]

Well I also need the Rank field. I think I would need to do a AddColumns but not sure on what calculation I would use to display the rank of each item.

[WarrenBelz]

@kubalaml ,

The Count sort will automatically sort in Rank order I thought. You can then simply display Rank.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[kubalaml]

Well the Rank column doesn't exist. I'm trying to calculate that based on their place in the sort order. 

[WarrenBelz · Accepted solution]

@kubalaml ,

This will add it

With(
   {
      wCount: 
      SortByColumns(
         Activities,
         "Count", 
         Descending,
         "User",
         Ascending
      )
   },
   ForAll(
      Sequence(CountRows(wCount)),
      Patch(
         Last(
            FirstN(
               wCount,
               Value
            )
         ),
         {Rank: Value}
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @kubalaml ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[kubalaml]

Hey @WarrenBelz sorry I've been crazy busy lately. I finally got to try out that formula last night. That is an amazing formula BTW. That's super creative using Sequence like that! I don't think it's 100% the solution because, using my example above, User A would be Rank 1 and User B would be Rank 2, but I need User A and User B to both be Rank 1 because they both have the same Count. 

With that being said though, this is exactly what I needed to get started. I think I can get what I need by comparing the previous count in a loop or something. 

Thanks!