Average percentage of school grades per subject and per category

[Anonymous]

Hello! I have three tables:

Grades, that contains students, subjects and the grades in letters:

Students	Subject	Grade
1	English	B
2	English	B
3	English	D
1	Maths	A
2	Maths	D
3	Maths	B
1	French	A
2	French	C
3	French	B
1	Latin	B
2	Latin	A
3	Latin	F
1	Biology	B
2	Biology	B
3	Biology	A

Percentages, that assigns a percentage value to each grade

Grade	Percentage
A	100
B	85
C	75
D	65
F	50

And Categories, which assigns a top category to each subject

Category	Subject
Language	English
Language	French
Language	Latin
Science	Maths
Science	Biology

Can someone help me to create a measure that gives me the average percentages per subject or per category in Power BI, i.e. a result like this:

Average per subject

English	78,33%
French	86,66%
Latin	78,33%
Maths	83,33%
Biology	90

Average per category

Languages	81,11%
Science	86,66%

I have tried the quick measures in Power BI but they do not seem to work. Then I found solutions like Average=Calculate(average(Percentages[Percentage]),Filter(Grades, (Grades[Subject])="Latin")), which is not exactly what I need because I need the measure to work for all grades and all categories and not just "Latin" as shown here. In addition, the correct average values are not calculated if a single grade occurs twice for a subject (see screenshot below). The average for "Latin" is correct but the one for "English" is not because the grade "B" occurs twice and then it just calculates the average for the two remaining grades. 

Subject	AverageLatin	AverageEnglish
English		75,00
Latin	78,33

Can someone help me with this?

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[Anonymous]

Hi, thank you for your solution, it works!

Someone also showed me this measure to solve the problem as well:

Avg Percent = AVERAGEX(Grades,RELATED(Percentages[Percentage]))

Best regards!