topic Re: Calculating average BEST score for student tests by subject in DAX Commands and Tips

Calculating average BEST score for student tests by subject

afaherty — Wed, 22 Sep 2021 20:31:31 GMT

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!

Re: Calculating average BEST score for student tests by subject

VahidDM — Wed, 22 Sep 2021 22:01:16 GMT

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🙏!!

Re: Calculating average BEST score for student tests by subject

PaulOlding — Thu, 23 Sep 2021 10:53:49 GMT

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?

Re: Calculating average BEST score for student tests by subject

afaherty — Thu, 23 Sep 2021 12:54:10 GMT

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.

Re: Calculating average BEST score for student tests by subject

afaherty — Thu, 23 Sep 2021 12:57:00 GMT

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