March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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!
Solved! Go to 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?
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.
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🙏!!
Hello, Paul is correct above - the Notes column is not actually included in my data. But thank you!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
31 | |
19 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |