Microsoft Office Tutorials and References
In Depth Information
FIguRE 9-5 Sample data in a Products table linked to a Sales table.
The measures PercentageOnTotal and PercentageOnModel are very similar to the ones
previously defined in the single table scenario.
PercentageOnTotal = SUM( Sales[SalesAmount] )
/ CALCULATE( SUM( Sales[SalesAmount] ),
ALL( Products ) )
The only difference in PercentageOnTotal is that the Products table is used to extend the
filter through the ALL function. In this way, a possible filter on the OrderDate column would
still apply, maintaining the dynamic calculation in the PivotTable.
In the PercentageOnModel measure, the only difference is that the Product column passed
to the ALL call belongs to the Products table and no longer to the Sales table.
PercentageOnModel = SUM( Sales[SalesAmount] )
/ CALCULATE( SUM( Sales[SalesAmount] ),
ALL( Products[Product] ) )
The results of these two measures are identical to those shown in Figure 9-2.
Now that you see how to define measures, which are dynamic by their nature, we look at
what you need to make a similar calculation using calculated columns, to get a calculation
that is not dynamically influenced by the selection in the PivotTable. In this scenario, you
usually have many rows in the Sales table for each row in the Product table. Because the
calculation needs to be at the Product level, you have to follow the relationship between
Product and Sales to compute the right value of SalesAmount for each product and each
model. First of all, you can calculate the total sales amount for each product by defining
the following ProductSalesAmount calculated column in the Products table:
ProductSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ) )
Search JabSto ::

Custom Search