Could someone explain this calculation with SUMX?

[Lamar249]

I realized that this was not needed but wanted to understand why this is not calculated correctly. 
To make this as simple as possible without screenshots, imagine I have 2 Measures (M1 & M2) for the same table with different Filters.

M1 = 10 
M2 = 30

In two other measures I have ways of calculating.
DAX1 = [M1]+[M2] 
which = 40

Dax2 = Calculate(SUMX([M1]+[M2]))
Totaling lets say 130
How and why is this happening, could be over looking something here but should this even happen? 

[Greg_Deckler]

@Lamar249 That doesn't look like valid DAX code, SUMX takes a table as a first parameter. If I had to guess, it would be performing the calculation of M1 and M2 for each row of the table, adding them together and then taking the total sum of those rows. But, hard to be sure given the information provided. Also, not sure the CALCULATE is entirely necessary, but that will depend on a lot of things.

[Lamar249]

You're right about it not being valid, when I typed out the question I forgot to put the table part here is a better example of what was being done. I'm fairly new to PBi, so keep in mind this before I realize I can simply just add all measures together. 

Entry = SUMX(Table1, [M1] + [M2] + [M3]+ [M4] + [M5] + [M6])
Let say the total is supposed to be 2,000 but the total is 2500 from the measure above. 
In my report, I traced the issue down to measure (M3) all other measures are calculated correctly. If I put [M3] in a visual by itself it will show the correct value but if I make a test measure and do SUMX(Table1, [M3]) just to isolate, it will have a different value than M3 on its own vurses SUMX. 

[Greg_Deckler]

@Lamar249 OK, what is the formula for the M3 measure? If it references other measures, will need those measure formulas as well. 

[Lamar249]

[Greg_Deckler]

 OK, so it's really hard to visualize exactly what is going on but here is the jist of it I think. So SUMX is going to calculate M3 for every row of Table1 and then sum all of those results together. Now, M3 is using CALCULATE and CALCULATE replaces filter context and discards row context (see below from dax.guide). So, for each row, any row context is being discarded and you are creating a sum of the rows in the table that fit the filter criteria specified. Then all of those values get summed up. If you want to see what is going on internally, then you could do this:Test Measure = VAR __Table = ADDCOLUMNS('Table1', "__M3", [M3]) VAR __Result = TOCSV( __Table, 100 ) RETURN __ResultYou could use that in a Card visual for example to see what is going on internally within the SUMX essentially, as in the table generated over which the sum is applied. I am *guessing* what is going on is that each row will have the same value because row context is being removed and then the same filter context is applied for each row. Just a guess.

And now you know why the last line of my signature is what it is...

From dax.guide:

CALCULATE evaluation follow these steps:

1. CALCULATE evaluates all the explicit filter arguments in the original evaluation context, each one independently from the others. This includes both the original row contexts (if any) and the original filter context. Once this evaluation is finished, CALCULATE starts building the new filter context.
2. CALCULATE makes a copy of the original filter context to prepare the new filter context. It discards the original row contexts, because the new evaluation context will not contain any row context.
3. CALCULATE performs the context transition. It uses the current value of columns in the original row contexts to provide a filter with a unique value for all the columns currently being iterated in the original row contexts. This filter may or may not contain one individual row. There is no guarantee that the new filter context contains a single row at this point. If there are no row contexts active, this step is skipped. Once all implicit filters created by the context transition are applied to the new filter context, CALCULATE moves on to the next step.
4. CALCULATE evaluates the CALCULATE modifiers used in filter arguments: USERELATIONSHIP, CROSSFILTER, ALL, ALLEXCEPT, ALLSELECTED, and ALLNOBLANKROW. This step happens after step 3. This is very important, because it means that one can...ALL as a filter argument. The CALCULATE modifiers are applied after the context transition, so they ...
5. CALCULATE applies the explicit filter arguments evaluated at 1. to the new filter context generated after step 4. These filter arguments are applied to the new filter context once the context transition has happened so they can overwrite it, after filter removal — their filter is not removed by any ALL* modifier — and after the relationship architecture has been updated. However, the evaluation of...KEEPFILTERS, the filter is added to the filter context without overwriting existing filters over the...

   The filter context generated after point (5) is the new filter context used by CALCULATE in the evaluation of its expression.

@Lamar249

[Lamar249]

With some trial and error, I narrowed it down to the entries in this particular data set that is having the issue. I used your measure above and sorted it to the same data so that I could compare it. There is an extra $81k in the SUMX measure, which I filtered down the dataset to where this is coming from.
I removed all the extra text from your measure to compare the filter query.

Filtered Query	Your Measure
39683.226	39683.226
-33670.616	-33670.616
33670.616	33670.616
-39683.226	-39683.226
40524.9914	40524.9914
-40524.9914	-40524.9914
40524.9914	81049.9828
-40524.9914	-40524.9914
39683.226	39683.226
-39683.226	-39683.226
40524.9914	40524.9914
-40524.9914	-40524.9914
40524.9914	81049.9828
-40524.9914	-40524.9914