cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

How to calculate rank in a collection

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. 

 

RankUserCount
1User A100
1User B100
3User C75
4User D50

    

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

@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.

View solution in original post

7 REPLIES 7
Super User III
Super User III

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.

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.

@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.

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

@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.

View solution in original post

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.

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!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (35,550)