I have been grappling with this for a few days and looked through various ‘Incorrect Total’ tutorials, but I am not having much luck so far.
A PBIX file is attached.
TotalExample.pbix (46.0 KB)
The model is simplified, but I think it has everything needed to replicate the problem. There are two dimension tables (student name and subject) and two fact tables (report data and predicted grades). They have 1 to many relationships between them.
For each subject, a student gets report grades at several points throughout the year. They also have grade predictions for most subjects. My calculations are:
- Average predicted grade
- Average trajectory (report) grade
- Value-add (trajectory minus predicted grade)-if a positive score, students are doing better than expected.
- A student’s average grade for all their subjects for a selected report cycle
- Difference between the report grade for a subject and the average for all subjects
- Value add for all subjects-the average trajectory minus predicted grade for a student for a selected report cycle
- Value add difference between selected subject and all subjects
Not all subjects have predicted grades. Not all students have all the subjects. Not all report cycles have trajectory grades for all subjects. The calculations mainly work on a row-by-row basis, but the totals are incorrect in pivot tables and the value-add for all subjects is incorrect.
Any help would be much appreciated.