Microsoft Office Tutorials and References

In Depth Information

FIguRE 9-2
Browsing data with percentage measures.

The advantage of defining the PercentageOnTotal and PercentageOnModel measures in the

PivotTable is that they are calculated dynamically when you apply any other filters on data for

both numerator and denominator of the ratios. Usually this is all that you need, but sometimes

you might want to make this calculation ignoring any filters made in the PivotTable. This might

be necessary, for example, when such a calculation is also used as part of another formula. In

these cases, you might consider defining calculated columns so that the calculation is made at

data-loading time.

The definition of the calculated column that returns the value of a product as a percentage of

all the products is similar to a measure you have seen before:

ProductOnTotal = Sales[SalesAmount] / SUM( Sales[SalesAmount] )

In this case, the numerator does not require a SUM aggregation because it needs to

operate only on the current row. The denominator contains simply the SUM aggregation

of SalesAmount, which in this case always sums all the rows of the Sales table.

To calculate the percentage on model, you first need to calculate the SalesAmount value for

the model. You use the ModelSalesAmount calculated column as the denominator of the

ProductOnModel ratio.

ModelSalesAmount = CALCULATE( SUM( Sales[SalesAmount] ),

ALLEXCEPT( Sales, Sales[Model] ) )