Microsoft Office Tutorials and References

In Depth Information

FIguRE 9-1
Sample data in a single table for ratio calculation.

You can create a PercentageOnTotal measure in the PivotTable that calculates the percentage

of sales for each product compared to the total of all the products.

PercentageOnTotal = SUM( Sales[SalesAmount] )

/ CALCULATE( SUM( Sales[SalesAmount] ),

ALL( Sales ) )

The calculation is a ratio that has a numerator with the SUM for all sales for the products that are

active in the filter context. In this way, if you select a group of products (such as all the products

of a certain model), that ratio still works. The denominator is also a SUM of the SalesAmount

measure, but it is included in a CALCULATE call, which removes any filter context from the whole

Sales table, using the ALL function.

In a similar way, you can also define a PercentageOnModel measure, which differs from the

previous only in its denominator.

PercentageOnModel = SUM( Sales[SalesAmount] )

/ CALCULATE( SUM( Sales[SalesAmount] ),

ALL( Sales[Product] ) )

In this case, the CALCULATE statement alters the filter context by removing filters only from

the Product column. Because the Model column is not affected, any filters on the Model

column still apply. So the CALCULATE statement applies the SUM aggregation on all the

products of the Model column to which the current products belong in the PivotTable. In

Figure 9-2, you can see the results of these two measures.