Averaging (rolling up)numbers in connected objects

I have an app which requires that I roll up individual scores to a connected record based on Month/Year.


Group 1:
Team A
Team B
Group 2:
Team X
Team Y
Team Z

Each team and group has a score for each month of the year. The team and group scores are stored in separate, connected objects. The scores for the team are dynamic and change daily throughout the month. The group scores for each month are the average of their assigned teams for the month and need to be calculated in real time.
Group 1- 35 pts
Team A - 60 pts
Team B - 10 pts

Group 2- 15 pts
Team X - 20 pts
Team Y - 10pts
Team Z - 0 pts

I have tried unsuccessfully to use the Average function to roll up the scores. There isn’t a filter that allows you to use connected values. You must use something like ‘Team is “XYZ”’. The input values change because there are multiple teams and multiple months.

I tried to use Make and the Average function for arrays, but I cannot get it to “see” the array and it’s values. It works with comma separated values which are keyed in but I can’t get it to work with passing it an array.

Does anyone have suggestions on how to create this roll-up score system. It seems so straightforward on paper but I am out of ideas.


It would work if you modify your groups table like this:

Group 1 - Sept 2023
Group 1 - Oct 2023
Group 2 - Sept 2023
Group 2 - Oct 2023

And enter the (child) team scores associated to the parent groups.