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.
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% |
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.
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.