Answered

Use related records in calculated column to calculate average score.

(0) Share

Report

Posted on by Usman2

124

In my canvas app I have two Dataverse tables Student and Test, one student can attempt many tests so the relationship is one-to-many.

In Test table I have the score column (Decimal number) which contains the score for the test that the student has attempted. Now in the Student table I want to create a calculated column which will contain the average score of the attempted tests by the student.

Example

Student A attempts 2 tests

Test 1 - Score 80

Test 2 - Score 90

Student A average score 85

Note: Average score must be saved in database.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @Usman2 ,

You can utilize Rollup fields for this purpose as rollup fields can do Avg as one of the calculations... (I know this references on premises but the details are the same):

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-rollup-fields?view=op-9-1

Here is a video showing details on rollup columns: https://youtu.be/ntk57m4A9ko

Was this reply helpful? Yes No
Usman2 124 on at

Like (0)

Report
Copy link

Link copied!

@dpoggemann
Thanks for the solution but now I have one more problem as I thought about it I think that the relationship between Student and Test will be N-to-N will this roll up columns still work. Because in the video the guy says it will not work for N-to-N relation.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Usman2 ,

No, this will not work with N:N relationships. My recommendation is to create an intersect entity like the following:

Student --> Student Test <-- Test

1:N from Student to Student Test

1:N from Test to Student Test

This way you can store metadata on the Student Test entity (including score) and you will be able to do the rollup on the Student to the Test scores or on the Test to the Student Tests...

Was this reply helpful? Yes No