Example: Market Index
In a product list (Product is in the drilldown), you want to display the sales revenue normalized for (based on) a specific product group rather than the total sales revenue. Using the Constant Selection function, you can select the sales revenue of a specific product group as the constant for the drilldown. You can now relate the sales revenue of the individual products in the product group to the sales revenue of the product group. This allows you to determine the revenue from each individual product as a proportion of the sales revenue for the product group.
The following example tables show how the table changes when the characteristic value Envelopes from the characteristic Product is removed from the filter. The second table shows how a change to the navigation or filter of the characteristic Product has no effect on the column containing the constant selection.
The key figures are defined as follows:
● Absolute Sales is the key figure Sales.
● Constant Sales (Selection) is a selection with the key figure Sales and characteristic Product. Constant selection is activated for characteristic Product.
● Normalized Sales (Formula) is a formula with 'Absolute Sales %A ‚ Constant Sales (Selection)’. %A is the percentage deviation. For more information, see Percentage Functions.
● Constant Sales (Formula with SUMCT) is a formula with the data function Result (SUMCT) on the key figure Sales. For more information, see Data Functions.
● Normalized Sales (Formula with SUMCT) is a formula with 'Absolute Sales %A ‚ Constant Sales (Formula with SUMCT)’. %A is the percentage deviation.
To help understand the concept of constant selection, we recommend that you first compare the Absolute Sales and Constant Sales (Selection) columns in the two tables. You can then compare the differences between the Constant Sales (Selection) and Constant Sales (Formula with SUMCT) columns. The two Normalized Sales columns represent typical situations.
Example Table for Market Index
Product Group |
Product |
Absolute Sales |
Constant Sales (Selection) |
Normalized Sales (Formula) |
Constant Sales (Formula with SUMCT) |
Normalized Sales (Formula with SUMCT) |
Office supplies |
Paper |
30 |
120 |
25% |
120 |
25% |
|
Envelopes |
30 |
120 |
25% |
120 |
25% |
|
Ballpoint pens |
60 |
120 |
50% |
120 |
50% |
|
Result for office supplies |
120 |
120 |
100% |
240 |
50% |
Furniture |
Chair |
60 |
120 |
50% |
120 |
50% |
|
Table |
60 |
120 |
50% |
120 |
50% |
|
Results for furniture |
120 |
120 |
100% |
240 |
50% |
Overall Result |
|
240 |
240 |
100% |
240 |
100% |
Example Table for Market Index with Different Filter Values
Product Group |
Product |
Absolute Sales |
Constant Sales (Selection) |
Normalized Sales (Formula) |
Constant Sales (Formula with SUMCT) |
Normalized Sales (Formula with SUMCT) |
Office supplies |
Paper |
30 |
120 |
25% |
90 |
33.3% |
|
Ballpoint pens |
60 |
120 |
50% |
90 |
66.6% |
|
Result for office supplies |
90 |
120 |
75% |
210 |
42.85% |
Furniture |
Chair |
60 |
120 |
50% |
120 |
50% |
|
Table |
60 |
120 |
50% |
120 |
50% |
|
Results for furniture |
120 |
120 |
100% |
210 |
57.15% |
Overall Result |
|
210 |
240 |
87.5% |
210 |
100% |
By removing Envelopes from the drilldown, the result for Office Supplies and the overall result for Absolute Sales is reduced by 30. This is a reduction of 25% with respect to the product group Office Supplies and 12.5% with respect to the overall result.