Percentage Share Parameters 
Use the Configure Percentage Share Parameters screen to configure parameters specific to the Percentage Share template. With this template, you can build queries that calculate the share that selected members represent within the sum of the level members. The result is expressed as a percentage.
This template can address business questions such as the following:
What percentages do Canada and Mexico represent of my total sales?
Structure
The controls in the main section of this screen, Calculate Share of Members of, are listed in the following table:
Control |
Description |
|---|---|
Dimension |
Lists all dimensions in the cube on which the query is based. Select the dimension that contains the hierarchy to display in the Hierarchy field. |
Hierarchy |
Lists all hierarchies in the currently selected dimension. Select the hierarchy that contains the level to display in the Level field. |
Level |
Lists all levels in the currently selected hierarchy. Select the level that contains the members you want to see in the query's result. The level name in the radio buttons below updates according to the value you select. |
As a percentage of the sum of all members of <level name> |
Calculates individual members' shares in proportion to the total for all level members. Note that defining filters (on the subsequent wizard screen) will have an effect on the way your result displays. |
As a percentage of the sum of all filtered members of <level name> |
Calculates individual members' shares in proportion to only those members you select in the subsequent step of defining a filter. You must define a filter (on the subsequent wizard screen) for this option to work properly. |
Based on measure |
Lists all measures in the cube on which the query is based. Select the measure upon which to base the calculation. |
Example
The table below illustrates the difference between basing the result on all level members and basing the result on filtered level members:
Product Family |
Unit Sales |
% Based on All |
% Based on Filtered |
|---|---|---|---|
Wine |
200 |
20% |
33% |
Beer |
200 |
20% |
33% |
Juice |
200 |
20% |
33% |
Whiskey |
200 |
20% |
|
Soft drinks |
200 |
20% |
In this case, the Product Family level has five members. You have selected three members (Wine, Beer, and Juice) on the Define Filters screen (which follows this screen in the wizard). To illustrate the effect of this template's different options:
As a percentage of the sum of all members of level Product Family:
The percentage share is based on the total for the whole level. In the table above, each member has 20% of the share of the total. If you wanted to define a query that tells you what percentage Canada and Mexico make up of your total sales, you would also use this option. In this case, you would filter your results to just select Canada and Mexico, and their percentages added together would not add up to 100% if there are other members contributing to the total that are simply not displayed.
As a percentage of the sum of all filtered members of level Product Family:
The percentage share is based on only the total of the filtered members. In the table above, each member takes 33% of this share. You might also use such a query to compare sales results among three sales representatives, excluding the rest of the group - essentially comparing themselves to each other within a subset of a level.
Note
The table above is for illustration purposes. The actual result of this query will show three columns: one for the level, one for the measure, and one for the percentage share.