Calculating average BEST score for student tests by subject

[afaherty]

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
Math	84.75
English	89
Writing	88.5

Here is the dummy data:

ID	Test	Score	Notes
1	Math	55
1	Math	75
1	Math	65	(this is #1's best Math score)
1	English	80
1	English	90	(this is #1's best English score)
1	Writing	87
1	Writing	98	(this is #1's best Writing score)
2	Math	90	(this is #2's best Math score)
2	English	76
2	English	84
2	English	90	(this is #2's best English score)
2	Writing	80	(this is #2's best Writing score)
2	Writing	70
2	Writing	65
3	Math	88	(this is #3's best Math score)
3	Math	76
3	English	92	(this is #3's best English score)
3	Writing	89	(this is #3's best Writing score)
3	Writing	79
4	Math	96	(this is #4's best Math score)
4	Math	86
4	English	74
4	English	84	(this is #4's best English score)
4	Writing	87	(this is #4's best Writing score)

Thanks to anyone who can help!

[PaulOlding · Accepted solution]

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?

[afaherty]

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]

Hi @afaherty 

Try this measure:

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

Output:

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

Appreciate your Kudos🙏!!

[afaherty]

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