Calculating Product Share and Market Share Values

This example calculates product share and market share values for each market and each product. The share values are calculated as follows:

Assume that you added four members to the Measures dimension:

Example script:

/* First consolidate the Sales values to ensure that they are accurate */

FIX(Sales)
   CALC DIM(Year, Market, Product);
ENDFIX

/* Calculate each market as a percentage of the total market for each product */

"Market Share" = Sales % Sales -> Market;

/* Calculate each product as a percentage of the total product for each market */

"Product Share" = Sales % Sales -> Product;

/* Calculate each market as a percentage of its parent for each product */

"Market %" = Sales % @PARENTVAL(Market, Sales);

/* Calculate each product as a percentage its parent for each market */

"Product %" = Sales % @PARENTVAL(Product, Sales);

Essbase performs the following actions:

  1. Fixes on the Sales values and consolidates all the Sales values.

    The CALC DIM command is used to calculate the Year, Market, and Product dimensions. The Measures dimension contains the Sales member and therefore is not consolidated. The Scenario dimension is label only and therefore does not need to be consolidated.

  2. Cycles through the database and calculates Market Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales in all markets for each product (Sales -> Market).

  3. Calculates Product Share by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of total Sales of all products in each market (Sales -> Product).

  4. Calculates Market % by taking the Sales value for each product in each market for each month and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Market dimension.

    The @PARENTVAL function is used to obtain the Sales value of the parent on the Market dimension.

  5. Calculates Product % by taking the Sales value for each product in each market for each month, and calculating this Sales value as a percentage of the Sales value of the parent of the current member on the Product dimension.

    The @PARENTVAL function is used to obtain the Sales value of the parent on the Product dimension.