Regarding to the concept of context transition

[HungFan]

Hi there,
I would like to ask a question about context transition, the problem I met is described as follows,
In my data model, Table 'Sales' and 'Product' has a Many to one relationship through [Product ID]. I want to add a calculated column in 'Product' to get the [ProductWithSalesQuantity] by means of context transition. The 1st formula I use is 

ProductWithSalesQuantity = CALCULATE(SUMX(Sales,'Sales'[Quantity]))

And the data shown up is what I expected.

However, the 2nd formula I use shows that context transition doesn't happen. The formula is 

ProductWithSalesQuantity V1 = SUMX('Sales',CALCULATE(SUM('Sales'[Quantity])))
Which I think by using the iterator of SUMX, CALCULATE shall trigger context transtion.

And then I try to add a Table as follows. By using the iterator of ADDCOLUMNS, CALCULATE does trigger context transition. The result is what I expected.

Could anyone guide me why the 2nd formula I use didn't work?

[tamerj1 · Accepted solution]

Hi @HungFan 

Aactually the context transition happens but inside SUMX which is iterating over the sales table. The code is actually returning SUMX ( Sales, Sales[Quantity] ). Using CALCULATE is turning the row context of the SALES TABLE in to a filter context. In other words it is doing nothing. You should be iterating over Products table in order to see the effect of context transition such as SUMX('Products',CALCULATE(SUM('Sales'[Quantity]))) however this iteration is not even required as you are already in the products table row context created by the calculated column itself. So you need only CALCULATE(SUM('Sales'[Quantity]))

[HungFan]

Thanks @tamerj1 , I will make some test again.