Microsoft Office Tutorials and References
In Depth Information
FIguRE 9-15 The SalesAmountProduct column has the same value for all the rows of each product.
use CALCuLATE Whenever Possible
Even if the CALCULATE syntax might not be intuitive when you use it in a calculated
column, it is usually faster than possible alternatives based on other aggregation
functions. For example, you might use the SUMX function by filtering all the products
that correspond to the one in the current row context.
SalesAmountProduct = SUMX( FILTER( Sales,
Sales[Product] = EARLIER( Sales[Product] ) ),
Because SUMX defines a new row context, you need to use the EARLIER function, which
gets the product name of the row context external to the one defined by the SUMX
function. However, remember that using CALCULATE is usually better for performance reasons.
The second calculated column is CumulatedProduct: this is the accumulated value of a product,
considered in descending order, from the top-seller to the worst.
CumulatedProduct = SUMX( FILTER( Sales,
>= EARLIER( Sales[SalesAmountProduct] ) ),
You repeat a pattern similar to the one shown in the previous sidebar by using SUMX as
a slower alternative to CALCULATE. The SUMX function filters all the sales transactions
belonging to a product that has a total sales for the product greater than or equal to the
sales of the product in the current row, outside of the SUMX row context. (You have to use
EARLIER in the same way you saw in the SalesAmountProduct formula based on SUMX.) In
other words, the FILTER function returns all the rows of the products that sold at least the
amount value of the current product. You can see in Figure 9-16 that, sorting transactions
by SalesAmountProduct in descending order, the CumulatedProduct for the top-selling
product has the same value of SalesAmountProduct, and then for the second product in
this ranking, the value is the sum of the first two products, and so on.