SUM of Measure not matching with SUMX Measure

[SK070281]

SKU Details Table

Created this measure: Total mg per SKU = 'SKU Details'[mg per pack] * 'SKU Details'[Pack Size]

Database Table

Total mg = CALCULATE(SUMX(Database,Database[Units] * RELATED( 'SKU Details'[Total mg per SKU])))

SUMX Total mg = SUMX(Database,[Total mg])

Result is as follow. Few products are matching few are not. Where am I doing mistake. Please help

Product	Total mg	SUMX Total mg	Difference
A	17,02,91,665	17,02,98,665	-7,000
B	19,17,87,360	19,17,90,384	-3,024
C	93,60,046	93,60,046	-
D	6,00,40,152	6,00,59,416	-19,264
E	4,27,22,920	4,27,22,920	-
F	1,58,26,39,275	1,58,28,14,475	-1,75,200
G	2,11,017	2,11,017	0
H	6,94,35,752	8,73,14,736	-1,78,78,984
I	3,57,40,638	3,57,41,870	-1,232
J	3,73,67,160	3,73,67,160	-
K	1,20,03,120	1,22,20,920	-2,17,800
L	11,65,876	11,65,876	-0

[AlexisOlson]

FYI, what you defined on the SKU Details Table is a calculated column, not a measure. A measure aggregates values rather than operating in a row context (if you tried to define a measure with that expression, it would throw an error).

Expanding your code:

SUMX Total mg =
SUMX (
    Database,
    CALCULATE (
        SUMX (
            Database, //This table is filtered by the row of the outer table
            Database[Units] * RELATED ( 'SKU Details'[Total mg per SKU] )
        )
    )
)

I suspect the reason you're getting a difference is that you have some non-unique rows in Database, in which case they're getting counted multiple times since for each row in the outer SUMX, it's pulling all matching rows in the inner SUMX. The [Total mg] measure does a context transition inside outer SUMX, which filters Database table in the inner SUMX to all the rows that match the filter context generated from the row context in the outer Database table.

I think you can just use this:

Total mg (No CALCULATE) =
SUMX (
    Database,
    Database[Units] * RELATED ( 'SKU Details'[Total mg per SKU] )
)