topic Re: Trying to understand usage/logic of SUMX (outcome is illogical) in DAX Commands and Tips

Trying to understand usage/logic of SUMX (outcome is illogical)

JustinDoh1 — Sun, 22 Aug 2021 15:50:17 GMT

I am trying to understand about "SUMX" in general (because somebody suggested to use it), but I am not sure how it works. I got the impression that SUMX counts all occurrence/evaluation in the row context.

I decided to use "SUMX" because I have cases where I have to make the outcome as "", and if I do not use SUMX, Total comes out to be "" in Maxtrix, and that is not it should look.

I am working on measures called "Step1_0821" & "Step1 0821 SUMX".

According to the logic, it should generate 1 in both"Step1_0821" & "Step1 0821 SUMX", but on "Step1 0821 SUMX", the outcome is 2, and that is why I am trying to get a feedback from the community.

If I understand the flow of logic correctly in this case, the logic did not meet the requirement for vNotEligible, so it goes to the final choice option, Step1, for the outcome of 1.

But, with the logic of Step1, it should generate only 1 row for Step1 0821 SUMX or I am writing the DAX formula correctly.

Where am I missing the logic?

Please help me. Thank you so much!

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

Greg_Deckler — Sun, 22 Aug 2021 15:53:39 GMT

JustinDoh1 Sorry, I don't see SUMX in your formulas? Reasons to use SUMX. SUMX accepts a table expression as the first parameter so you can filter it however you please and then SUMX takes the second parameter, evaluates the expression for each row of the table and then sums the results together. You can see the difference between SUMX and CALCULATE. Take a simple table like below:

ItemQuantityValue

One	5	5
Two	10	10
Three	20	20

And the following measures:

Sumx1 = SUMX(FILTER('Items',[Item]<>"Two"),[Quantity]*[Value]) Caculate1 = CALCULATE(SUM('Items'[Quantity])*SUM('Items'[Value]),'Items'[Item]<>"Two")

SUMX returns the correct answer 425 while CALCULATE returns the wrong answer 625. SUMX got the right answer because it evaluated at the row level, 5*5 + 20*20. CALCULATE got it wrong because it calculated (20 + 5) * (20 + 5).

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

JustinDoh1 — Sun, 22 Aug 2021 16:31:00 GMT

Greg_Deckler Thank you for your feedback. I was using SUMX calling measure "Step1_0821" like this:

So, you suggest to use SUMX on this area directly?

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

Greg_Deckler — Sun, 22 Aug 2021 18:01:28 GMT

JustinDoh1 Right, in general you either use CALCULATE or SUMX. When you mix the two you could definitely get some odd results.

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

JustinDoh1 — Mon, 23 Aug 2021 04:26:17 GMT

Greg_Deckler Thank you so much. I am getting close to make this work.

When I tried with two criteria within filter, it would not give me error.

But when I tried with more than two, it would not let me, but gives me an error.

Do I go about doing this?

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

Greg_Deckler — Mon, 23 Aug 2021 09:58:27 GMT

JustinDoh1 Your NOT(ISBLANK is missing a closing paren. Should be

NOT(ISBLANK(Table1[Consent]))

Re: Trying to understand usage/logic of SUMX (outcome is illogical)

justindoh — Mon, 23 Aug 2021 12:37:22 GMT

Greg_Deckler Thank you so much. I was searching for an example online for "SUMX multiple filters", and only found examples up to 2 filters, so I lost a confidence.