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] ) )