Find 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

The second table displays the cutpoints used to determine their letter grade.

Min	Max	Grade
87	100	A
75	86	B
62	74	C
44	61	D
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. How do I go about looking up the grade based on the average test results?

[ryan_mayu · Accepted solution]

@CityCat35 

you can try this to get the grade

Column = maxx(FILTER(Table2,'Table'[Test Result]>=Table2[Min]&&'Table'[Test Result]<=Table2[Max]),Table2[Grade])

[Joe_Barry · Accepted solution]

Hi @CityCat35 

Create a measure

Average Score =
AVERAGE(Exams[Result])

Then to get the Grade

Average Grade =
IF([Average Score] >= 87, "A",
IF([Average Score] >= 75 && [Average Score] <= 86, "B",
IF([Average Score] >= 62 && [Average Score] <= 74, "C",
IF([Average Score] >=  44 && [Average Score] <= 61, "D",
IF([Average Score] <= 43, "F", BLANK()) 

Add the Student and Subject columns to a Table visual and then the measures

Hope this helps

Joe

[Anonymous · Accepted solution]

Hi @CityCat35 ,

Thanks @Joe_Barry  and @ryan_mayu  for the quick reply. Please allow me to provide additional insights:

(1)We can create measures.

Avg = AVERAGE('Table'[Test Result])

Grade = 
MAXX(FILTER(ALLSELECTED('Table (2)'),[Avg]>='Table (2)'[Min] && [Avg]<='Table (2)'[Max]),[Grade])

(2) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Joe_Barry · Accepted solution]

Hi @CityCat35 

Create a measure

Average Score =
AVERAGE(Exams[Result])

Then to get the Grade

Average Grade =
IF([Average Score] >= 87, "A",
IF([Average Score] >= 75 && [Average Score] <= 86, "B",
IF([Average Score] >= 62 && [Average Score] <= 74, "C",
IF([Average Score] >=  44 && [Average Score] <= 61, "D",
IF([Average Score] <= 43, "F", BLANK()) 

Add the Student and Subject columns to a Table visual and then the measures

Hope this helps

Joe

[ryan_mayu · Accepted solution]

@CityCat35 

you can try this to get the grade

Column = maxx(FILTER(Table2,'Table'[Test Result]>=Table2[Min]&&'Table'[Test Result]<=Table2[Max]),Table2[Grade])