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.
Search JabSto ::

Custom Search