Price * Volume Formulas
You can create a revenue formula (price * volume) when using a planning model, or other formulas based on lookup functions.
Consider this sales planning scenario:
- The total product sales quantity (by number) is planned by region and product
(and also Time and Version). A sales revenue forecast would be calculated based
on this formula:
Sales Revenue = (Sales Quantity of Product in Region) * (Price of the Product in Currency)
- Depending on the region, different products are planned.
- The Price of the product is the same in all regions.
Issues with this scenario:
Identical price for all regions
When prices are identical for all regions, you need to enter the price only once for all regions.
To accomplish this, you'll need to create an interim account to store the prices of the different products. In our scenario, the interim account is named "storedPrice/Stored Price". In the Region dimension, use the unassigned region member ("#") to store the price. The interim account is hidden so that users of the model won't see it.
The account that is used to show the price to users is named "price/Price". It retrieves its values from the "storedPrice" account using a lookup formula. This formula looks in the "storedPrice" account for the unassigned location ("#") and uses it for all regions.
LOOKUP([storedPrice] ,[d/Location] = "#" ,[d/Location] )
No Aggregation of prices
Semantically, it isn't useful to sum the prices of different types of bread:
| Region | ||
|---|---|---|
| Product | Switzerland | Brazil |
| Bread | No aggregation | No aggregation |
| White | 1 | 1 |
| Whole Grain | 2 | 2 |
| Bread | No aggregation | No aggregation |
| White | 1 | 1 |
| Whole Grain | 2 | 2 |
In this scenario, choose the aggregation type NONE for the accounts that include Price data:
Calculation Order for Price * Volume
For each type of bread, Revenue is calculated as Price * Volume.
However, for the parent item Bread, Revenue should not be calculated as Price * Volume, because there is no aggregation type for Price that would provide a meaningful value in a Price * Volume calculation. Instead, Revenue should be calculated as the sum of the child items:
| Region | Product | Price | Volume | Revenue |
|---|---|---|---|---|
| Switzerland | Bread | 49 | 81 | |
| White | 1 | 17 | 17 | |
| Whole Grain | 2 | 32 | 64 | |
| Brazil | Bread | 53 | 114 | |
| Whole Grain | 2 | 45 | 90 | |
| Rye | 3 | 8 | 24 |
The calculation of Revenue by multiplying the Price with Volume needs to be done before summing the volumes for the different bread types. When summing the different contributions of the regions for one product, the Volumes for these regions can be aggregated before multiplying with the price for the product:
| Product | Price | Volume | Revenue |
|---|---|---|---|
| Bread | 102 | 195 | |
| White | 1 | 17 | 17 |
| Whole Grain | 2 | 77 | 154 |
| Rye | 3 | 8 | 24 |
You can define two aggregation types for an account: Aggregation Type and Exception Aggregation Type. The exception aggregation type is optional, and requires one or more exception aggregation dimensions that are associated with the exception aggregation type. Using both aggregation types allows for flexible definition of aggregations and calculations.
SAP Analytics Cloud first attempts to aggregate the data as much as possible, and then processes any calculations. If no exception aggregation is defined, this process applies to all dimensions of the model. If exception aggregation is defined, the dimensions that are defined as exception aggregation dimensions are excluded from this processing. The exception aggregation is then processed after the calculations.
Note that defining an exception aggregation and choosing exception aggregation dimensions is also useful if the account is not calculated by a formula. However, in this case, the exception aggregation type should be different from the standard aggregation type.
In our example, we define the exception aggregation type to be SUM, and the formula to be [price] * [volume]. This means that the other dimensions that may be aggregated before calculating the formula are Region and Time.
