Calculating sum of top grades, ignoring earlier grades in the same subject

[SchoolDataGuy]

Hi,

My first post in the forum, I do my best to express myself as clear as I can, although I find that a bit challenging.

I want to calculate the sum of the highest school grades for each student up to each school year in a dataset.  I have used TOPN in order to calculate sum of the n highest grades. However, I need to deal with the fact that some student have several grades registered in the same subject, and only one should be used in the calculations for each school year.

Current solution

The dataset and measure is rather complicated but here is a simplified example for one student:

Grade table:

Index	Student ID	Subject	Grade	Grade date
1	1	Math	10	2020
2	1	English	15	2021
3	1	Physics	15	2022
4	1	Biology	12	2022

I calculate the sum of the top 3 grades:

Gradescore =

CALCULATE (

    SUM ( Grade[Grade] ),

    TOPN ( 3, Grade, Grade[Grade], 0, Grade[Index], 0 )

This yields the total result: 42. (The index is just there to deal with ties). By filtering the measure for school year, I can then easily calculate a cumulative grade sum for each student and year in a related table. In this case resulting in the following:

Student/year table:

Student ID	School Year	Grade Score
1	2020	10
1	2021	25
1	2022	42

Problem and desired outcome

However. Sometimes the student has two grades registered in the same subject, and they should only receive credits for the highest one so far. I want the sum in the resulting table to include only the highest grade in each subject up to each specific school year.

Example:

Student ID	Subject	Grade	Grade date
2	Math	10	2022
2	English	15	2022
2	English	17	2023
2	Physics	15	2023
2	Biology	12	2024

In the current solution this will return the value 47 when calculating the top three grades, because English is counted twice. I want the English grade to be included only once (thus yielding the total result 44). A correct calculation of the cumulative grade for each year should look like this.

Student ID	School year	Grade score
2	2022	25
2	2023	42
2	2024	44

[SchoolDataGuy]

One issue that comes up is the memory use when implemented in my larger model with some thousand students and several fact tables.

The new solution becomes very slow and sometimes fails to visualize because it runs out of memories. Anys tips on how to make it perform better in this more complex environment?

[AnalyticsWizard · Accepted solution]

@SchoolDataGuy 

To solve your problem of counting only the highest grade per subject up to each school year while calculating the cumulative grade sum, you need to adjust your DAX calculations to account for the highest grade per subject and then apply the `TOPN` function. Here's how you can achieve this:

Create a Measure for Highest Grade per Subject and Year
First, create a measure that determines the highest grade for each subject per student up to the end of each school year. This will ensure that only the highest grade per subject is considered for each year.

Highest Grade Per Subject =
CALCULATE(
MAX(Grade[Grade]),
FILTER(
ALLEXCEPT(Grade, Grade[Student ID], Grade[Subject]),
Grade[Grade Date] <= MAX(Grade[Grade Date])
)
)

This measure uses `ALLEXCEPT` to keep the context of `Student ID` and `Subject`, ensuring the calculation groups by these dimensions. It filters grades to only include those up to the maximum `Grade Date` in the current context, allowing it to dynamically adjust for each year.

Calculate Top N Grades Including Only the Highest per Subject
Now, modify your existing measure to use the new `Highest Grade Per Subject` measure. The goal is to apply `TOPN` to this adjusted context:

Grade Score =
VAR TopGrades =
TOPN(
3,
SUMMARIZE(
Grade,
Grade[Student ID],
Grade[Subject],
"HighestGrade", [Highest Grade Per Subject]
),
[Highest Grade Per Subject], DESC
)
RETURN
CALCULATE(
SUMX(TopGrades, [HighestGrade])
)

This measure creates a summary table that groups by `Student ID` and `Subject`, calculating the highest grade per subject using the measure defined earlier. `TOPN` is then applied to select the top 3 entries based on the highest grades. `SUMX` is used to sum these top grades.

Use the Measure in Your Visuals
Add this measure to your visuals, filtered by `Student ID` and `School Year`, to show the cumulative highest grades up to each year. It dynamically adjusts, only considering the highest grade per subject and only including the top grades based on your requirement.

Make sure that your `Grade Date` and `School Year` mappings are correctly set up so that the comparisons and filters work as expected. This setup should handle cases where students have multiple grades for the same subject in the same year by ensuring that only the highest grade is considered for each subject in each year's calculation.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[SchoolDataGuy]

Very clearly put. Thank you so much!