Student Grade Report

[Crashwave]

Hello All
I was wondering if I can get some assistance
I have three tables of student info

Table 1 has their information

St_ID|First Name|Last Name|...|Status

Each student is in Table 1 once. Status is either C for current student, G for graduated, or D for dropped out.

St_ID	First Name	Middle	Last name	Status
1	First1	M1	L1	A
2	First2	M2	L2	D
3	First3	M3	L3	A
4	First4	M4	L4	D
5	First5	M5	L5	D
6	First6	M6	L6	G
7	First7	M7	L7	D
8	First8	M8	L8	G
9	First9	M9	L9	A
10	First10	M10	L10	A
11	First11	M11	L11	A
12	First12	M12	L12	G
13	First13	M13	L13	A
14	First14	M14	L14	A
15	First15	M15	L15	A

Table 2 has the year, semester. course #, class # they took and the grade for each class. 
St_ID|Year|Semester|CourseID|Class No| Letter Grade | GPA

Year	Term	St_ID	Course	Class	Grade	GPA
2020	3	1	Cr1	Cl1	A	4
2020	3	1	Cr2	Cl2	B+	3.3
2020	3	1	Cr3	Cl3	C	2
2020	3	1	Cr4	Cl4	A	4
2020	3	1	Cr5	Cl5	A-	3.7
2020	3	2	Cr1	Cl2	F	0
2020	3	2	Cr2	Cl3	C	2
2020	3	2	Cr3	Cl4	C+	2.3
2020	3	2	Cr4	Cl5	D	1
2020	3	2	Cr5	Cl6	C-	1.7
2021	1	3	Cr1	Cl1	A	4
2021	1	3	Cr2	Cl4	B+	3.3
2021	1	3	Cr3	Cl3	C	2
2021	1	3	Cr4	Cl6	A	4
2021	1	3	Cr5	Cl5	A-	3.7

Table 3 has a score (or scores) of tests they must take besides the courses
St_ID| Year|Semester| T1|T2|T3|...| cumulative score

we are only interested in the max score achieved of the total score

Year	Term	St_ID	Total
2020	3	1	2.3
2020	2	1	3.3
2020	3	2	2
2020	1	2	2.7
2020	2	2	2.3
2020	1	3	2.2
2020	2	3	2.5
2020	3	3	3
2020	2	4	2.3
2020	3	4	2
2021	1	7	3.5

What I have now, which could probably be done much much better, are 2 drop down slicers of the year and semester, two cards displays total students in the semester, the total that are active, total graduated and the total that have dropped out. 

it also displays a table of the students per year & semester selected and their course grades 

We have formatted the table and can sort it and filter it by Student, course and grade 

What I am trying to do next is bring in Table 3 with the scores as maybe a filter or slicer  (ideally a text input) to display students in the same table that have a selected score, from the slicer/filter/text box.

EDIT: for example list all students for year/semester whith their grades who have a score < 2.5

For some reason I cannot bring in the scores correctly and we don't want to show it multiple times per course grade.

Ideally we would want to expand to show a graphs and such on those failed or bad grades/scores and maybe those who graduated but for now we want an idea where student stands in selected year/term

Any assistance appreciated
Thanks

[ChrisMendoza · Accepted solution]

@Crashwave -

I suggest you look into the 'Q & A' visual. I think my proposed is close to your desired output, if anything it might give you or someone an idea. Find the attached.

[Anonymous]

Hi. 

I have a similar request. I need to load data from an excel spreadsheet that has rows with the students subjects and their result for that subject. I need one page per student with their results and it must be emailed. I want to use Powebi for the visuals as I would like to have a bar geaph to show the results

[ChrisMendoza]

@Anonymous - Is it one single table vs. the (3) in this OG thread? Can you provide sample data?

If you're looking for a mail/email merge solution to what I believe is your circumstance you can use my blog posts Mail-Merge Data Preparation – Excel-ing (csuchico.edu) & Mail-Merge Excel to Word – Excel-ing (csuchico.edu)