topic Re: SUMX total not "adding up" in DAX Commands and Tips

SUMX total not "adding up"

DataSkills — Thu, 15 Dec 2022 17:18:25 GMT

Hi, I am confused about the result of a DAX formula that is giving an unexpected result.

Notice how the totals for the 2 columns don't add up? This is a filtered table.

Total care mins = sum(Table1[Scores])

Test = CALCULATE(sumx(values(Table1[PatientName]),max(Table1[Scores])))

The ACTUAL values from the data are:

Scores

129

246

213

243

I just cannot get my head around why these 2 totals are different. If I remove all the filters, they are still different so I don't think this is the reason.

What am I missing?

Thanks

Re: SUMX total not "adding up"

JW_van_Holst — Thu, 15 Dec 2022 18:08:38 GMT

In the total there is no filter on patient. Sumx is iterating over all 5 patients. While iterating it evaluates the second argument (MAX). Because there is no filter context it will evaluate every time the max of all patients (246). In the last step it will aggrate the five 246 in an sum (1260).

Re: SUMX total not "adding up"

DataSkills — Thu, 15 Dec 2022 18:23:19 GMT

Thank you JW_van_Holst! I now get where this number comes from. That makes sense to me.