Why is my SUMX DAX function returning this result?

Question

Suppose I have 2 tables:

fTransactions

ProdID  RepID  Revenue
1       1      10
1       1      10
1       2      10

dSalesReps

RepID   RepName
1       joe
2       sue

With dSalesReps having the following measures with no filters applied yet:

RepSales:=CALCULATE(SUM(fTransactions[Revenue]))
RepSales2:=SUMX(fTransactions, CALCULATE(SUM(fTransactions[Revenue]))

The first measure performs how I think it would. It goes to the fTransactions table and sums up the Revenue column.

The second measure, after a lot of trial and error to figure it out, seems to sort of group itself on unique rows in fTransactions. In the above example, fTransactions has 2 rows where everything is identical, then a last row where something is different. This seems to result in the following:

(10 + 10) first iteration that sums the first "grouping"
+
(10 + 10) second iteration that sums the first "grouping" again
+
(10) last iteration that sums the second "grouping"

= 20 + 20 + 10 = 50

At least that's how it looks to be operating. I just don't understand why. I thought it would go to the fTransactions table, sum all of Revenue for each iteration, then sum those sums as a final step.

The problem is the duplicate rows. Check this post sqlkover.com/sumx-returns-incorrect-results-with-duplicates — Marco Vos

Giovanni Luisotto Giovanni Luisotto · Accepted Answer · 2020-03-13T10:17:09

This is caused by something called "context-transition" (see sqlbi more detailed explanation).

In practice, your formula "RepSales" uses a "Row Context" (created by SUMX) which is turned in an equivalent "Filter Context" (by CALCULATE), but since you don't have an unique key in the table, it gets and uses multiple rows in each iteration, below the explanation.

For the first row, the row context is ProdID=1 AND RepID=1, which turned in an equivalent filter context (stays the same, in this case) is ProdID=1 AND RepID=1 but the filter context is global, and two rows (the first 2) match this filter. This is repeated for each row.

it does not happen with the formula "RepSales" because it does not iterate multiple times (as you already noticed)

This is your current situation:

To prove that, just add a rowID to the transaction table:

It does not happen because the equivalent filter context also include the RowID column, which matches only one row

Hope this helps, use the sqlbi article as a reference, it will be an exhaustive guide to understand this

Why is my SUMX DAX function returning this result?

1 Answers