Calculation wrong in matrix

[Kimber]

I'm trying to figure out how to structure my matrix so the subtotals calculate correctly.

My data looks like:

Student ID	Grade when earned	Term when earned	Subject	Course Title	Mark	Credit Earned	Credit Expected by Now	Credit Deficiency As of Now
4	9	S1	Math	Algebra 1	D	0.5	2.0	0.50
4	9	S2	Math	Algebra 1	D	0.5	2.0	0.50
4	10	S1	Math	Geometry	D	0.5	2.0	0.50
4	10	S2	Math	Geometry	F	0	2.0	0.50
5	9	S1	English	English 9	B	0.5	3.0	2.00
5	9	S2	English	English 9	B	0.5	3.0	2.00
5	9	S1	Math	Algebra 1	B	0.5	3.0	1.00
5	9	S2	Math	Algebra 1	B	0.5	3.0	1.00
5	10	S1	Math	Geometry	C	0.5	3.0	1.00
5	10	S2	Math	Geometry	C	0.5	3.0	1.00
5	11	S1	Math	Algebra 2	F	0	3.0	1.00
5	11	S2	Math	Algebra 2	F	0	3.0	1.00

I want to create a matrix that lists all the courses a student has taken in a subject and the credit earned in each course. I want to subtotal the number of credits earned by each student in each subject. Finally, I want to show how many credits a student is currently deficient in the subtotal row.

It should look like this (which is filtered to show only math), but I can't figure out how to make the subtotals come out correctly for the number of credits earned. If I use MIN(CreditCompleted) the subtotal is wrong.

If I use SUMX(CreditCompleted) the calculation is wrong for the course AND the subtotal.

Here is how my matrix is structured:

What do I need to do to calculate the credits earned correctly?

[Anonymous]

Hi @Kimber 

Based on your sample data, Sum of Credit Earned should work. 

However from your screenshots, I guess one student may have multiple records of credit for the same mark/course/term/grade/subject, so you choose to use MIN to get the credit in the first screenshot. Based on this, your target will be to sum all Min values. To get this result, you can create a new table to have the distinct min credit for each mark/course/term/grade/subject which summarizes data from the original table, then use columns from the new table to populate the matrix visual and use SUM for aggregation. 

Here is a DAX method for the new summarization table:

Table 2 = SUMMARIZE('DuplicateRecordTable', 'DuplicateRecordTable'[Student ID],'DuplicateRecordTable'[Subject],'DuplicateRecordTable'[Grade when earned],'DuplicateRecordTable'[Term when earned],'DuplicateRecordTable'[Course Title],'DuplicateRecordTable'[Mark],"Credit",MIN('DuplicateRecordTable'[Credit Earned]))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Wilson_]

Kimber,

I think you're looking for just SUM ( Table1[Credits Earned] ), not SUMX.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.