cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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:

 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?

3 ACCEPTED SOLUTIONS
Super User

you can try this to get the grade

Proud to be a Super User!

Super User

Create a measure

``````Average Score =
AVERAGE(Exams[Result])``````

``````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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Community Support

Hi @CityCat35 ,

(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 3
Community Support

Hi @CityCat35 ,

(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.

Super User

Create a measure

``````Average Score =
AVERAGE(Exams[Result])``````

``````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

Proud to be a Super User!

Learn about the Star Schema, it will solve many issues in Power BI!

Super User

you can try this to get the grade

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.