cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afaherty
Helper II
Helper II

Calculating average BEST score for student tests by subject

Hello all,

I am attempting (and failing) to calculate the average BEST score for student tests by subject (Math, English, and Writing) and ignore their non-best scores.

 

Here's my goal:

 Average Best Score
Math84.75
English89
Writing88.5

 

Here is the dummy data:

 

IDTestScoreNotes
1Math55 
1Math75 
1Math65(this is #1's best Math score)
1English80 
1English90(this is #1's best English score)
1Writing87 
1Writing98(this is #1's best Writing score)
2Math90(this is #2's best Math score)
2English76 
2English84 
2English90(this is #2's best English score)
2Writing80(this is #2's best Writing score)
2Writing70 
2Writing65 
3Math88(this is #3's best Math score)
3Math76 
3English92(this is #3's best English score)
3Writing89(this is #3's best Writing score)
3Writing79 
4Math96(this is #4's best Math score)
4Math86 
4English74 
4English84(this is #4's best English score)
4Writing87(this is #4's best Writing score)

 

Thanks to anyone who can help!

1 ACCEPTED SOLUTION
PaulOlding
Solution Specialist
Solution Specialist

If you have the Notes column on the real data then VahidDM's solution is likely to be the fastest.

When I read the question I assumed the Notes column isn't in the real data.  If that's the case a measure like this will get your answer.

 

Average Best Score = 
AVERAGEX(
    SUMMARIZE('Table', 'Table'[ID], 'Table'[Test]),
    CALCULATE(MAX('Table'[Score]))
)

 

*btw your note for ID 1's best Math score doesn't look right.  Shouldn't it be on the line above - score = 75?

View solution in original post

4 REPLIES 4
PaulOlding
Solution Specialist
Solution Specialist

If you have the Notes column on the real data then VahidDM's solution is likely to be the fastest.

When I read the question I assumed the Notes column isn't in the real data.  If that's the case a measure like this will get your answer.

 

Average Best Score = 
AVERAGEX(
    SUMMARIZE('Table', 'Table'[ID], 'Table'[Test]),
    CALCULATE(MAX('Table'[Score]))
)

 

*btw your note for ID 1's best Math score doesn't look right.  Shouldn't it be on the line above - score = 75?

View solution in original post

Thank you so much! Yes you're correct, I had noted #1's highest math score incorrectly.  My apologies, I was about to race out of the office before posting this and my brain was somewhere else!

You are the best!  Many many thanks.

VahidDM
Memorable Member
Memorable Member

Hi @afaherty 

 

Try this measure:

verage Best = 
CALCULATE (
    AVERAGE ( 'Table'[Score] ),
    FILTER ( 'Table', 'Table'[Notes] <> "" )
)

 

Output:

VahidDM_0-1632348047112.png

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

 

Hello, Paul is correct above - the Notes column is not actually included in my data.  But thank you!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!