Show TOC

Using Constant Selection for a Market IndexLocate this document in the navigation structure

Use

In a product list ( product is in drilldown), you do not want to see the absolute sales revenue. Instead you want to see the sales revenue normalized for (in relation to) a specific product group. Using the property Constant Selection, you can select the sales revenue of a specific product group as a constant in relation to the drilldown. You can relate the sales revenue of each product in this product group to the sales revenue for the product group and, for example, work out the percentage share of sales revenue for each product in the product group.

Procedure

Define the key figures as follows:

  • Absolute Revenue is the key figure Revenue.

  • Constant Revenue (Constant Selection CSC) is a selection with the key figure Revenue and the characteristic Product. Constant selection is activated for the Product characteristic.

  • Normalized Revenue (Formula) is a formula with ' Absolute Revenue %A' Constant Revenue (Selection)'. %A is the percentage deviation. More information: percentage functions.

  • Constant Revenue (formula with SUMCT) is a formula with the data function Result (SUMCT), which relates to the key figure Revenue. More information: Data Functions.

  • Normalized Revenue (formula with SUMCT) is a formula with ' Constant Absolute Revenue %A (formula with SUMCT)'. %A is the percentage deviation.

Product Group

Product

Absolute Revenue

Constant Revenue (Constant Selection CSC)

Normalized Revenue (Formula)

Constant Revenue (Formula with SUMCT)

Normalized Revenue (Formula with SUMCT)

Office Supplies

Paper

30

120

25%

120

25%

Envelopes

30

120

25%

120

25%

Pens

60

120

50%

120

50%

Result: Office Supplies

120

120

100%

240

50%

Furniture

Chair

60

120

50%

120

50%

Table

60

120

50%

120

50%

Result: Furniture

120

120

100%

240

50%

Overall Result

240

240

100%

240

100%

Result

The following example table shows how the table changes, if the characteristic value Envelopes of the Product characteristic is removed from the filter. You will see that changing the navigation or using a filter for the Product characteristic has no effect on the column for constant selection.

Note

To make it easier to understand constant selection, we recommend that you initially only compare the columns Absolute Revenue and Constant Revenue (Constant Selection CSC) in both tables. Afterwards you can compare the differences between the columns Constant Revenue (Constant Selection CSC) and Constant Revenue (Formula with SUMCT). The two columns with Normalized Revenue illustrate a typical use case.

Product Group

Product

Absolute Revenue

Constant Revenue (Constant Selection CSC)

Normalized Revenue (Formula)

Constant Revenue (Formula with SUMCT)

Normalized Revenue (Formula with SUMCT)

Office Supplies

Paper

30

120

25%

90

33,3%

Pens

60

120

50%

90

66,6%

Result: Office Supplies

90

120

75%

210

42,85%

Furniture

Chair

60

120

50%

120

50%

Table

60

120

50%

120

50%

Result: Furniture

120

120

100%

210

57,15%

Overall Result

210

240

87,5%

210

100%

Removing the characteristic value Envelopes has reduced the Result: Office Supplies and the Total Result from Absolute Revenue by 30. This represents 25% in relation to the Office Supplies product group or 12.5% in relation to the total result.

More Information