I have been working on getting a project going, and am running into some difficulty. I was hoping someone may be able to guide me in the right direction. I am looking for recommended approaches.
My scenario is: I'm trying to create a "Productivity Tracker" for students, that puts students into a category based on weekly grade data, and some other criteria (whether student is active, whether they are enrolled in specific courses, etc). My main problem is that the data includes multiple grades (for all courses a student is in), and as a result, a student can fall into more than one category. What I really want is for each student to only fall into one category. It seems the most logical way to do this would be to group the grades (percent) by each student, and only use the maximum for the category calculation--as that is the main driving factor in how the categories are evaluated. We are only concerned with their highest possible grade percentage for that student, in terms of which category the student falls into.
I am attaching a sample workbook that shows one sheet, with a student list, their category, and some filters so my scenario is as obvious as I can make it.
We could create a view that only returns the maximum grade/percent, grouped by student, but we also want to look at these categories by course, by teacher, etc. so I'm not sure that makes sense. I'm sure a table calc could be used to find the maximum in a layout like the one attached, but I need to try and make it work at several levels of detail--and the overall calculation for the categories needs to only count each student once.
If anyone wants to do an online meeting of sorts to look at this in more detail, let me know. I'm really just looking for guidance--how should we approach this? Is creating another view of the data, grouped by student, the most viable option? Will data blending work for this scenario? I'm just not knowledgeable enough to know what the best solution here is.
I do know that Table Calcs would allow me to get at most, if not all, of the information I want--but I just don't know if that is the best option and I'm not very experienced with them. Jonathan Drummey already helped me see one way that Table calcs could be used to get to the count of students by category, but it was a bit over my head and I'm not sure if I could adapt it for every look into the data that is required.