cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Data Source Calculation (Multiple Rows where Column Equals X)

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 

 

data.PNG

 

The end result should look something like this

 

leader board.PNG

 

please help 🙂 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

6 REPLIES 6
Super User III
Super User III

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.

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.

View solution in original post

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

Hi @tapper ,

Thanks for the comments - this should work

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

 

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

Thanks @tapper ,

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (12,906)