position of calculate()

[Jeanxyz]

I'm learning the Definitive Guide to DAX and get confused with context transition.

I understand when calculate() is used, DAX will search for existing row context(s) and convert it into filtering context.

So I have written a calculated column in product table as below:

Product[AverageSalesPerCustomer2] = calculate(Averagex(Customer,sum(Sales[Quantity])))

I think two row contexts will be applied to the calculate() formula: the customer row context and product row context. The formula will filter in current customer and current product (row context from product table), get the total sales quantity, then move to next customer, get total quantity of current product...., and calculate the average of all customers. But it turns out the product row context is not considered here. As a result, I get very high values. 

The correct measure seems to be: 

Product[AverageSalesPerCustomer] = Averagex(Customer,calculate(sum(Sales[Quantity])))

Why the first measure doesn't work?

[AlexisOlson]

In this calculated column,

Product[AverageSalesPerCustomer] = AVERAGEX ( Customer, CALCULATE ( SUM ( Sales[Quantity] ) ) )

CALCULATE performs a context transition that transforms the Customer row context (separately for each row that AVERAGEX is iterating over) and also the Product row context (from the current row of the Product table) into filter context.

In this calculated column,

Product[AverageSalesPerCustomer2] = CALCULATE ( AVERAGEX ( Customer, SUM ( Sales[Quantity] ) ) )

CALCULATE performs a context transition on Product but there's nothing to perform the context transition for each row of Customer, so row context for each customer is not considered. To transition the row context within an iterator, you need CALCULATE inside of the iterator. 

As a side note, if you defined a measure SumQuantity = SUM ( Sales[Quantity] ) and wrote,

Product[AverageSalesPerCustomer3] = CALCULATE ( AVERAGEX ( Customer, [SumQuantity] ) )

 this would work as expected since calling a measure implicitly wraps a CALCULATE around the measure expression.

[Jeanxyz · Accepted solution]

Thanks @AlexisOlson .

I did some thinking on calculate(), here is my expanded explanation of the filtering process. 

1. Calculate copies any existing filter or row context, then it evaluates any new filter/row context within the calculate() formula.

2.If there is a row context, Calculate converts the row context into filter context via context transition.

3. Calculate then combines the exsting context with the new context (from inside calcualte()) and applies the combined one to relevant tables and expressions.

In the formula below, the existing context is a row context from the Product table as this is a calculated column in Product table. Calculate finds the product row context and converts it into a filter context, which is then applied to expression - AVERAGEX ( Customer, SUM ( Sales[Quantity] ) ). In other words, Sales table is filtered by product table, Customer table however is not filtered because there is no relationship between Product table and Customer table.

It important to note that at this moment, the row context from Customer table is not considered because it is created inside calculate function.

Next, calculate evaluates  any new context wrapped in calculate(), it finds a new row context from Customer table. However, this row context is not passed onto sum(Sales[Quantity]) as this formula is not wrapped in calculate(). In that case, sum(Sales[Quantity]) sums up Quantities of the all the rows in the visible context,  in this case, all the rows as defined by the product row context.

In reality, the formula doesn't use  any customer filter. It simply reference customer A, adds up all the quantity of product A regardless the customer selection, record the sum of product A (imagine we are on the row of  product A),  then it moves to customer B, but again it adds up the sum of product A regardless the customer selection, then customer C.... Once done, the formula calcualate the average sales of all customers, because each customer row contains the same amount, i.e., total quantity  of product A, the averagex() yields of total quantity of product A. 

Product[AverageSalesPerCustomer2] = CALCULATE ( AVERAGEX ( Customer, SUM ( Sales[Quantity] ) ) )

So to fix the bug, I need to wrap Sum(Sales[Quantity]) with calculate() so that the Customer row context is passed onto the formula. 

[AlexisOlson]

I think you've got it. 🙂

[Jeanxyz]

Hi @AlexisOlson , 

You have helped me with row context before, could you please have a look at my question above?

I have uploaded the sample file.

https://www.dropbox.com/s/nss4yo99m3kb7cb/Contoso.pbix?dl=0

I would like to count average sales quantity per customer per product. Product[AverageSalesPerCustomer] gives me the correct the result, however, I would like to understand why Product[AverageSalesPerCustomer2]  doesn't give the same reuslts. It seems to add up sales quantity per product,  ignores customer row context and Averagex() calculation. 

[bcdobbs]

Sorry I went back to the definitive guide to dax. The bottom of page 113 explains it. Your second expression is correct because when you call calculate inside the averagex both row context are active. (Ignore previous post)

In your first expression only the row context on the product table is active where calculate is called.

[Jeanxyz]

Do you understand why the first measure doesn't work as expected, why product row context is not used?

[bcdobbs]

Hi,

I'm not by a computer but I think you actually want both an outer and inner calculate. Eg a combination of the two measures.

Your first converts the product row into a filter context but sums sales for all customers.

The second converts the customer row to a filter context but sums all products.

I think from your question you want to do both?

[Jeanxyz]

I just don't understand why the product row context is not applied in the first measure since the book says calculate() will search for existing row contexts, and convert them to a filter context.

The 2nd measure loops through all customers and the average sales quantity of the current product.