Forum Discussion

CityCat35's avatar
CityCat35
Frequent Visitor
2 years ago
Solved

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

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

 

StudentCourseTest Result
BobbyScience92
SarahMath99
TimEnglish100
BobbyScience86
SarahMath77
TimEnglish80
BobbyScience61
SarahMath60
TimEnglish70

 

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

 

MinMaxGrade
87100A
7586B
6274C
4461D
043F

 

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?

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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's avatar
    Joe_Barry
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    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's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    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