SUMX with ALLEXCEPT not working?

[IncursioML1]

Hi All

In the below I added a column with the DAX logic trying to sum up all quantities relating to a specific RelatedOrderText but getting an odd value on the newly created column.  The math is adding up to 349 where if you sum up the quantities it's adding up to 53.  What am I missing here? Is there a sort I should do first?  

NonZeroBalance = SUMX(CALCULATETABLE(VALUES(Sheet1[Quantity]),ALLEXCEPT(Sheet1,Sheet1[RelatedOrderNumberText])),Sheet1[Quantity]) 

RelatedOrderNumberText	Quantity	NonZeroBalance
-12494281	-20	349
-12494281	-214	349
-12494281	-321	349
-12494281	-135	349
-12494281	-270	349
-12494281	-270	349
-12494281	-405	349
-12494281	-26	349
-12494281	-26	349
-12494281	1740	349

[SpartaBI · Accepted solution]

@IncursioML1 

Hope this helps 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but in my opionion, the difference between the expectation and the result is because of the duplicated values. There are two -270s and two -26s.

[IncursioML1]

Ah so the math is 

270+26+53(the correct balance) = 349.

How do I actually write the DAX to just sum up all quantities based on the related order text regardless of the quantities?

Below is the expectation.

RelatedOrderNumberText	Quantity	NonZeroBalance
-12494281	-20	53
-12494281	-214	53
-12494281	-321	53
-12494281	-135	53
-12494281	-270	53
-12494281	-270	53
-12494281	-405	53
-12494281	-26	53
-12494281	-26	53
-12494281	1740	53