Data Source Calculation (Multiple Rows where Column Equals X)

[tapper]

Hey all, 

I'm at my whits end and need some help. Currently I'm building an app where users submit their type of commute to work which creates a eco score for their journey. 

I'm really struggling to create the leader board table, where the eco scores for each user is calculated and presented on a leader board. 

Below is an example of my collected data, I would like to show a leader board where the TotalEntryScore is calculated for each UserName 

The end result should look something like this

please help 🙂 

[WarrenBelz]

Hi @tapper ,

Looking at your data set CommuteData, you could try getting BuzzLightyear's TotalEntryScore total with something like

Sum(
   Filter(
      CommuteData,UserName = "BuzzLightyear"
   ),
   TotalEntryScore
)

however Sum is not a delegable function in SharePoint, so you need to collect it (this is limited to 2000 records in the data source)

I will also include in the code below selecting any user in a drop down called UserList  with the user in a field called UserName

First collect the data

ClearCollect(
   colCommute,
   CommuteData
)

 The content of a label for the total would be

Sum(
   Filter(
      ColCommute,UserName = UserList.Selected.UserName
   ),
   TotalEntryScore
)

All of the above is guidance showing you the possibilities. You could also display a filtered gallery and calculate a total on that.

I hope some of this helps.

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 · Accepted solution]

Hi @tapper ,
I had another idea after I posted - it is a bit more complex, but I got it working on a test app gallery with the following Items property - it is using the GroupBy function and adds a column on items grouped by UserName to total TotalEntryScore.

AddColumns(
    GroupBy(
        CommuteData,
        "UserName",
        "EntryScore"
    ),
    "TotalScore",
    Sum(
        EntryScore,
        TotalEntryScore
    )
)

EntryScore and TotalScore can be whatever names you want - the rest refer to your data
The fields in the gallery would be ThisItem.UserName and ThisItem.TotalScore

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.

[tapper]

@WarrenBelz  You are invincible!! 

That has worked perfectly! You are a life saver. 

Do you have any tips on how I can add a sort function into that code so that the highest score is at the top? 

[WarrenBelz]

Hi @tapper ,

Thanks for the comments - this should work

Sort(
   AddColumns(
      GroupBy(
         CommuteData,
         "UserName",
         "EntryScore"
       ),
       "TotalScore",
       Sum(
          EntryScore,
          TotalEntryScore
       )
   ),
   TotalScore,
   Descending
)

[tapper]

hi @WarrenBelz  The filter worked perfectly Thank you!! 🙂 

[WarrenBelz]

Thanks @tapper ,

Please give it the Thumbs up if you liked it - makes it easier to find for others.