Showing crosstab values as percentages

When you work with dimensional data sources, you can show crosstab values as a percentage of a summary instead of the actual values. When you show values as a percentage, you can compare the contribution of an item to the whole.

About this task

You can show crosstab values as a percentage of a summary on the rows, the columns, or the rows and columns. For example, a crosstab has Product line as rows, Year as columns, and Revenue as the measure. You can show the following crosstab values:

The actual values of Revenue
A percentage of a summary on Product line
A percentage of a summary on Year
A percentage of a summary on Product line, Year

By default, the summary type that is used to calculate the percentages is Automatic summary.

When you show values as a percentage, a percentage measure data item is created.

You can also create a custom percentage calculation. For example, you can show values as a percentage of an intersection (tuple). Or you can show values as a percentage of a different summary type, such as Maximum.

When you show values as a percentage, the fact cells are formatted with the percent data format. When you show the actual values, the fact cells are formatted with the number format. If you modify the data format of the actual values, the change is lost when you switch between showing the actual values and percentage values.

You can show values as a percentage only in crosstabs. If you insert a chart, you cannot fill the chart with data from a crosstab that shows values as a percentage. If you create a chart from a crosstab that shows values as percentages, an empty chart is created in the report.

Procedure

Select the measure for which you want to show values as a percentage.
If there is only one measure in the crosstab, click the crosstab corner.
From the Data menu, click Show Value As, and click the percentage values that you want to show.
If you click Custom, provide the information that is required to calculate the percentage values.
1. To change the default name of the percentage data item that you are creating, type the new name in the Name field.
2. In the Percentage based on box, choose to build the percentage data item based on a summary of sets in the crosstab or based on an intersection (tuple).
3. If you choose to build the percentage data item based on a summary of sets, in the Choose sets box, click the sets that you want to use. Then, click the Summary type menu and select the summary that you want to use to calculate the percentage values.
  If there is more than one data item on the crosstab edge that you chose for the sets, select which data item that you want to use.
4. If you choose to build the percentage data item based on an intersection (tuple), click the ellipsis and select the items that you want to use to create the intersection.
5. Click OK.
To edit a measure percentage data item, follow these steps.
1. Select the measure.
2. From the Data menu, click Show Value As > Edit.
3. Modify the information for the data item as described in step 3.
4. If you replaced a set in the crosstab with a different set, in the Choose sets box, click Existing sets when you want to base the percentage values on the set that you replaced.