Lookup values from one table based on calculated result from another table

[CityCat35]

I have two tables. The first is a list of all the test scores of my students for each class I teach:

Student	Course	Test Result
Bobby	Science	92
Sarah	Math	99
Tim	English	100
Bobby	Science	86
Sarah	Math	77
Tim	English	80
Bobby	Science	61
Sarah	Math	60
Tim	English	70

Each course has its own grading system. The second table displays the cutpoints used to determine their letter grade.

Course	Min	Max	Grade
Science	87	100	A
Science	75	86	B
Science	62	74	C
Science	44	61	D
Science	0	43	F
Math	83	100	A
Math	76	82	B
Math	81	66	C
Math	54	65	D
Math	0	53	F
English	85	100	A
English	75	84	B
English	62	74	C
English	61	44	D
English	0	43	F

I'm using a matrix visual to display the average test results by student/course, and I also want to automatically show the letter grade based on the average results--but I'm stuck. Is there a way to find the grade based on the class and average score?

[ThxAlot]

You can simplify it at least in 2 aspects,

1. Keep Min only as threshold for each grade bucket;
2. Use single-direction filtering in *:* relation (in spite of best practice of 1:*)

  

For fun only, a showcase of power Excel formulas, (the score in red is to verify the fomula)

[hnguy71 · Accepted solution]

Hi @CityCat35 

To bring both data together, you'll most likely would need a third table to bridge the two together. There are more than one grade per course, and there are multiple students per course.

By the way, your Grade table has some inconsistencies. Min and Max are swapped on some rows.

You'll then link the two together via relationships. Do take note of the cross-filter direction on one line (not needed at the moment but I do suspect you may want to eventually use it down the line for another evaluation)

Create your average test score measure:

Course.Avg.Number = AVERAGE(StudentScores[Test Result])

And then finally in another measure, based on the average, return your grade label:

Course.Avg.Grade = 
VAR _Number = [Course.Avg.Number]
RETURN
CALCULATE(MAX(Grading[Grade]), _Number >= Grading[Min] && _Number <= Grading[Max] )

 
Your output should look like this: