Forum Discussion
values() vs row context
In this case, it's using the row context from the table argument values(sales). It iterates over each row in this table using the row context from this table.
When you write values(sales[product category]), this does not contain the [sales amount] and [retail margin] columns, so these cannot be from that row context and it instead interprets it as the original row context.
When you're working with calculated columns there isn't any filter context unless CALCULATE is involved. So when you reference a column, it assumes that you are either referring to the value in the current row (row context) or referencing the entire column (it assumes this when you put a column name inside an aggregation function).
In your formula, values(sales[ product category]) is the list {"1A", "2A", "3A"} so it's iterating through the three items and adding the result of sales[sales amount*sales[retail margin] for each iteration, effectively multiplying it by three.
Thanks, Alexis.
I'm still a bit confused with row context. There are actually two row contexts, the old row context comes from the calculated column, a new row context is created when a values() formula is created. Not sure which row context is used here.
If I change the values() formula to the values(sales), it seems to iterate each row based on the row context (not sure if it's the new row context or old row context as they are the same) and sum up the total. In other words, it doesn't multiple by
sales[sales amount]*sales[retail margin] by 13 times (i.e., there are 13 rows in the values(sales) table).Why the new calculated column behaves differerntly?In this case, it's using the row context from the table argument values(sales). It iterates over each row in this table using the row context from this table.
When you write values(sales[product category]), this does not contain the [sales amount] and [retail margin] columns, so these cannot be from that row context and it instead interprets it as the original row context.
