@mosy2 - if we just focused on what you're trying to do (rather than troubleshoot whatever solution you have already come up with), then as I understand it, you have a SharePoint List called "Modules" with displays a unique list of "ModuleIDs". You have another SharePoint List called "Assessments" which displays multiple ModuleIDs. This list includes a Number column called "SubmissionRatePercentage".
What I think you're trying to do:
You want to add a column into the Modules List which displays the average SubmissionRatePercentage for each Module. The way you want to calculate this is by summing the SubmissionRatePercentage value / the count of each module.
Example one:
AddColumns(
Modules As data,
SubmissionRateAvg,
Sum(
Filter(
Assessments,
ModuleID = data.ModuleID
),
SubmissionRatePercentage
) / CountRows(
Filter(
Assessments,
ModuleID = data.ModuleID
)
)
)
Example two (similar to the above but you're only calling the Assessments List once):
With(
{
_grouped_data: AddColumns(
Modules As _data,
FilteredTable,
Filter(
Assessments,
ModuleID = _data.ModuleID
)
)
},
AddColumns(
_grouped_data,
SubmissionRateAvg,
Sum(
FilteredTable,
SubmissionRatePercentage
) / CountRows(FilteredTable)
)
)
Example three (using GroupBy and LookUp):
With(
{
_grouped_data: AddColumns(
GroupBy(
Assessments,
ModuleID,
GroupedItems
),
SubmissionRateAvg,
Sum(
GroupedItems,
SubmissionRatePercentage
) / CountRows(GroupedItems)
)
},
AddColumns(
Modules,
SubmissionRateAvg,
LookUp(
_grouped_data,
ModuleID = Modules[@ModuleID],
SubmissionRateAvg
)
)
)
Notes:
- All of the above examples leverage the AddColumns function, which is not delegable with any data source.
- I notice you're using LookUp data types on the Module IDs to relate both lists. The above assumes the Module List and the Assessments List contains a column called "ModuleID". You will need to modify the above code to suit your needs/data.
- You may want to consider using the Average function, but the above is what you asked for.
- [Edit] I have just noticed @FLMike has responded to this thread. I would endorse all of his points - you will get to an answer quicker if you can provide a clear explanation as this post currently reads like a puzzle.
