Setting Up Displaying of Cell Value in Percents to Subtotal

Total values can be displayed for any data area. Sometimes it is required to display not the cell value itself, but its percentage to subtotal of the corresponding level or to grand total. This calculation type can be set up only for separate columns or rows and when several dimensions are placed in rows or columns. To make this point more visual, it is possible to set up displaying in such a way that some columns or rows display absolute values, and some display percentage.

Source Data

The Cube of Socio-Economic Indicators cube will be used to create a data area. Creation of this cube is described in the Example of Creating a Cube section.

Add this cube to the Data Sources and Data Slices area of the Data Sources and Data Slices panel. Create a slice that will be used to plot data area. Place slice dimensions as follows:

Columns. Calendar.
Rows. Territory dimensions.
Fixed. Socio-economic Indicators, Data Sources, Facts.

Set selection in dimensions and place the data area on a regular report sheet.

On placing the data area on the report sheet, select the Place Level Elements in Separate Columns and All Parents of Elements not Excluded should be Included in the Hierarchy checkboxes on the Layout > Rows tab of the Data Area Properties dialog box:

Select the Allocate only Leaf Elements item on the Layout > Rows > Socio-Economic Indicators Dimension tab and the Layout > Rows > Territories Dimension:

Calculate the data area.

Solution

Select any cell in this area and select the Data Area Parameters context menu item.
In the Data Area Properties dialog box go to the Totals > Rows tab and determine the following settings:

Select the Totals Based on Hierarchy checkbox.
Select the Sum item in the Totals Calculation Method combo-box.

Thus, displaying the sum of indicators by dimension child elements is set up:

Click the Set Up Formatting button in the Data Formatting area (the button for opening the dialog box is highlighted on the above screenshot).
In the Cell Format dialog box go to the Number Format tab and select the Percentage data format.
Go to the Layout > Columns > Dimension "Calendar" > Layout Setup tab and determine the following settings:

Select the Enable Custom Layout checkbox.
In the Dimension Elements area select 1996 and 1997 elements, and then click the Add into Layout button.

The selected dimension elements are added to the Top Header Layout area:

Select the 1996 element in the Top Header Layout area and click the button.
Determine the following settings in the Element Properties dialog box:

In the Formula area create the following formula: VALUE / SUBTOTAL.
Click the Set up Formatting button in the Data Formatting area. In the Cell Format dialog box go to the Number Format tab and select the Percentage data format (this dialog box is identical to the one given above for totals format selection). Define the same settings for the 1997 element:

Calculate the data area for the settings to apply.

After this the data area looks as follows:

Thus, displaying the data area cell value in percents to subtotal is set up. To estimate the element value, use total value of upper dimension element. For example, to estimate the value for Belgorod Region, the Total (Data of State Committee) subtotal is taken. To estimate this value, in its turn, the Total (Gross Regional Product) value is taken, and so on.

Set up totals based on hierarchy as described in the example above, but in the Cell Format dialog box on the Number Format tab select the Number data format.
Go to the Layout > Columns > Dimension "Calendar" > Layout Setup tab of the Data Area Properties dialog box. Select the Enable Custom Layout checkbox. Select the 1996 element in the Dimension Elements area and then double-click the Add into Layout button:

Select the second element 1996 in the Left Header Layout area and click the button.
Determine the following settings in the Element Properties dialog box:

Change the name to 1996 (%) in the Text box.
In the Formula area create the following formula: VALUE / SUBTOTAL * 100.
Click the Set up Formatting button in the Data Formatting area. In the Cell Format dialog box go to the Number Format tab and select the Number data format (this dialog box is identical to the one given above for totals format selection).

Calculate the data area for the settings to apply.

After this the data area looks as follows:

The parameters have been set up in such a way, that one column displays the percentage to subtotal, and another one displays elements' numeric values.

Setting Up Displaying of Cell Value in Percents to Subtotal

Question

Application Area

Source Data

Solution