Forum Discussion
Helper ISUMX with ALLEXCEPT not working?
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 |
3 Replies
- SpartaBI
Community Champion
- Jihwan_Kim
Super User
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.
IncursioML1Ah 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
