I have a student list that I use to report multiple years credit totals. I sometimes want to report this list of totals by grade level, which I know how to do. What i can't figure out is how can I show the students for the grade level they were two or three years ago. So, for instance in 2017 my 13th graders were 10th graders and I want to summarize their total credits, and show them as 10th graders. Their current grade level in my data is 13th grade. My list of students has their grade level and with a separate list of their courses, the course list doesn't include their current grade level and contains all of the terms that they completed the courses.
My goal is to be able to create tables and graphs in a dashboard. My data model looks to many external files and PowerQueries so it don't believe I can send you my sample.
Hi Cheryl,
It's very difficult to understand and help without seeing the data structure, even screenshots would help, but a mockup of the data in an Excel workbook would be ideal.
It might be possible to create relationships between the different tables to allow a PivotTable to report (as I understand it) to include the following fields:
- Name (rows)
- Year (columns)
- Grade (columns)
- Course (rows)
- Total Credits (value field)
Can you provide a sample file? You can copy extracts of your various tables and paste them into Power Pivot
Mynda
Okay - I am attaching a sample file that hopefully will help. I have loaded my queries to tables and the data models, but they are sourced from files on my work network. The dashboard is quickly thrown together to hopefully demonstrate how I want this to work. Attached also is a bit better explanation of what I am aiming for and description our organizations activities.
Are slicers only able to control pivot tables, and not charts that were created from the power pivot model?
Hi Cheryl,
Thanks for taking the time to create a mock-up file. There is no way I would have understood the issues without it, so it has been worth your time 🙂
The Extract table is a dimension table and this all looks fine. However, you want to be able to filter the data in this table based on a specific 'School Year' and this school year information is not in this table. The solution, is to bring in the fields like Ethnicity, Gender etc. that you want to report at a school year level to the College Grades table. You can use the RELATED function in Power Pivot to add calculated columns, or you could bring these columns in with a merge of the tables in Power Query before the data gets to Power Pivot, which would be more efficient.
The YearGradeLevel and GradeLevelYears tables can be deleted as they do not satisfy the requirement of dimension tables; i.e. at least one column must contain unique values and they must be in tabular formats, which YearGradeLevel is not.
Finally, Slicers can control both PivotTables and Pivot Charts for Power Pivot. Yours wouldn't work because your model isn't set up right yet.
I hope that points you in the right direction.
Mynda
