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] ) )
Search JabSto ::




Custom Search