Percent of Grand Total

In a Sales report you might well want to create a column which calculates for any one line, what it is, as a percentage of the total.

For example:

A suitable expression can be built using the "Advanced" button in the calculations dialog.

The calculation itself, is a cross dimensional.

It starts off by taking [Measures].[Balance] and dividing it by [Measures].[Balance]

However, we don't want to divide by the same [Measures].[Balance] in each cell, but instead by the one in the cell for the grand total at the bottom of the report, which can be found using [Company].[GrandTotal]

Hence: [Measures].[Balance]/[Measures].[Balance][Company].[GrandTotal]

This last bit in bold can be added, by clicking on the Advanced button as shown, and selecting "Use Grand Total"

Percent of a Specific Subtotal

Slightly less common might be a scenario, where you want to divide by a particular Subtotal, in the example above, perhaps for the subtotal of Chicago.

The expression for this would read:

[Measures].[Balance]/[Measures].[Balance][Segment2].[00|Subtotal]

In this case [Segment2].[00] happens to be the Segment2 code for Chicago which is then postfixed with |Subtotal

The UI will help you create this by selecting Segment2 in the member picker, and then 00 for Chicago. Once Chicago is selected, the "Use Subtotal" item is enable for you to click on as below:

Percent of a each items own Subtotal

A more useful scenario, might be where you want to divide by the subtotal which includes the current row.

The expression for this would read:

[Measures].[Balance]/[Measures].[Balance][Segment2].[<Asterisk>|Subtotal]

The UI will help you create this by selecting Segment2 in the member picker, and then <Asterisk> for "Each". Once that is selected, the "Use Subtotal" item is enable for you to click on as below: