Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
afaherty
Helper IV
Helper IV

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 Sage
Solution Sage

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 Sage
Solution Sage

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?

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
Super User
Super User

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors