Announcements
I need a way to aggregate data from a SharePoint list, then display the average. I have this Combined Score column I want to aggregate the scores by the Risk ID, then display the average (by Risk ID) in a field on a PA form.The scores can have anywhere from 1 to 11 submissions for a particular risk type (ID).
Hi @pbrainard ,
Try using this
AddColumns( GroupBy( YourListName, "Risk ID", "Data", ), "RiskAverage", Average( Data, 'Combined Score' ) )
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.
Visit my blog Practical Power Apps
Thank you. Got this error, Using this:AddColumns(GroupBy( colRiskScores, "RiskID", "DATA"), "RiskAverage", Average( Data, 'Combined Score'))
Maybe, nevermind the above...I was able to group my data by the RiskID, with all the data for each in the DATA2 tables. How then do I average the Combined Scores from the tabled data to be able to show that average in a text label?APP OnStart:
Where are you using that - it is valid code for the Items of a Gallery.
I tried to add your code to my OnStart of the App. Should I add it to the Items property of the gallery? I'm hoping the Average can be a value I can pull into another gallery who's IDs match the Risk ID from the Grouped Data.
I think our posts crossed - you do not need the first collection
ClearCollect( colGroupData, AddColumns( GroupBy( 'Risk Scores', "RiskID", "Data" ), "RiskAverage", Average( Data, 'Combined Score' ) ) )
Oooohh... That worked!!!! (minus the comma after Data in GroupBy). I'm able then to reference that in my gallery in a text label? The RiskID below corresponds to the ID of the gallery items (pulled from another table).
This is the current Text property of the gallery item I want this average score to populate. Right now it's just pulling a value from the Risk Scoring Table SharePoint list. I do not need to write this average back to SharePoint. Only display it here (and on the subsequent form).
@pbrainard ,
Yes - fixed the comma (dangers of free-typing without red lines to guide . . ).
It should be (assuming the field name here)
Average( GalleryName.AllItems, 'Combined Score' )
This is what I got...
I'm confused, a little, is this calling on the RiskAverage from the collection, and then doing something like (RiskID = ID) where ID is the gallery item (from a SharePoint list)? That's what I was imagining. 🙂
I thought you wanted the average of the indicated items in the gallery and the Label would be outside the gallery. You cannot (as you have found) use that inside a gallery referring to itself. What are the Items of the gallery you are displaying?
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
Congratulations to our community stars!
Expanding mentorship, skilling, and AI innovation
These are the community rock stars!
Stay up to date on forum activity by subscribing.
11manish 394 Super User 2026 Season 2
Mohsin Ali 328
WarrenBelz 324 Most Valuable Professional
