Microsoft Office Tutorials and References
In Depth Information
CALCuLATE vs. SuMX
Remember that the CALCULATE call transforms the row context into a filter context. For
this reason, the current row of the Products table (to which ProductSalesAmount belongs)
defines a filter for all the related rows in the Sales table, following the relationship between
Products and Sales. In other words, the ProductSalesAmount definition returns the same
results of the following expression:
= SUMX( RELATEDTABLE( Sales ), Sales[SalesAmount] )
However, you should prefer the CALCULATE version for performance reasons. You use
the SUMX version whenever you need to define a more complex expression for each
row of the table specified in the first parameter (something that would not be possible
by using CALCULATE).
The ProductOnTotal calculated column can be defined by using the SUM of the sales for all
the products in the denominator or the ratio:
ProductOnTotal = Products[ProductSalesAmount] / SUM( Products[ProductSalesAmount] )
Note Even if it were possible to use SUM( Sales[SalesAmount] ) instead of
SUM( Products[ProductSalesAmount] ) in the ProductOnTotalDefinition, in
this case, it is faster to use the column that has a lower number of rows.
The calculation of ProductOnModel still requires that you define the value of the sales for
each model in advance. The ModelSalesAmount column can be defined in the same way as
in the single table example, just by using the Products table instead of the Sales table in the
ALLEXCEPT filter. In the same way, the ProductOnModel has a definition similar to that for a
single table—you can just change the name of the referenced table.
ModelSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ),
ALLEXCEPT( Products, Products[Model] ) )
ProductOnModel = Products[ProductSalesAmount] / Products[ModelSalesAmount]
Finally, the Products table shown in Figure 9-6 contains all the new calculated measures,
including the percentages that you might use in other calculations.
FIguRE 9-6 Calculated columns for product percentages of total and model.