How to Create Calculated Key Figures with Exception Aggregation in SAP BW/4HANA
SAP BW/4HANA is a powerful data warehousing solution that enables you to create, analyze and report on key performance indicators (KPIs) for your business. One of the features of SAP BW/4HANA is the ability to define calculated key figures, which are derived from existing key figures using formulas. Calculated key figures can help you perform complex calculations, comparisons and conditional logic on your data.
In some cases, you may want to apply a different aggregation method to a calculated key figure than the standard one. For example, you may want to calculate the average of all values that are not zero, or the sum of all values that meet a certain condition. This is where exception aggregation comes in handy. Exception aggregation is a way of defining a specific aggregation method for a calculated key figure based on a reference characteristic or dimension.
In this blog post, we will show you how to create calculated key figures with exception aggregation in SAP BW/4HANA. We will explain what exception aggregation is, how it works, and how to use it in different scenarios.
What is Exception Aggregation in SAP BW/4HANA?
Exception aggregation is a type of aggregation that can be applied to a calculated key figure in SAP BW/4HANA. It allows you to specify a different aggregation method for a calculated key figure than the standard one, based on a reference characteristic or dimension.
The reference characteristic or dimension is used to group the data before applying the exception aggregation method. For example, if you want to calculate the average of all values that are not zero for each product category, you can use product category as the reference characteristic and average of all values not zero as the exception aggregation method.
The following table summarizes the exception aggregation methods that are supported in SAP BW/4HANA:
| Exception Aggregation | Description | Example |
|---|---|---|
| Count All Values [CNT] | Count all values regardless of their sign or value. | CNT (Sales Quantity) returns the number of sales quantity records. |
| Constant with exception aggregation SUM | Sum all values regardless of their sign or value. | SUM (1) returns the number of records. |
| Multiple-dimensional exception aggregation SUM for a constant | Sum all values regardless of their sign or value for each combination of reference characteristics. | SUM (1) with Product Category and Region as reference characteristics returns the number of records for each product category and region combination. |
| Total [SUM] | Sum all values with the same sign. | SUM (Sales Revenue) returns the total sales revenue. |
| Minimum [MIN] | Return the minimum value among all values. | MIN (Sales Revenue) returns the lowest sales revenue. |
| Maximum [MAX] | Return the maximum value among all values. | MAX (Sales Revenue) returns the highest sales revenue. |
| Count All Values <> 0 [CN0] | Count all values that are not zero regardless of their sign. | CN0 (Sales Quantity) returns the number of sales quantity records that are not zero. |
| Average of All Values [AVG] | Calculate the average of all values regardless of their sign or value. | AVG (Sales Revenue) returns the average sales revenue. |
| Average of All Values < > 0 [AV0] | Calculate the average of all values that are not zero regardless of their sign. | AV0 (Sales Revenue) returns the average sales revenue excluding zero values. |
| First Value [FIR] | Return the first value in the order defined by the reference characteristic or dimension. | FIR (Sales Revenue) with Month as reference characteristic returns the sales revenue of the first month. |
| Last Value [LAST] | Return the last value in the order defined by the reference characteristic or dimension. | LAST (Sales Revenue) with Month as reference characteristic returns the sales revenue of the last month. |
| No aggregation if more than one record arises [NO1] | Return no aggregation if more than one record arises for each combination of reference characteristics. Otherwise, return the value of the record. | NO1 (Sales Revenue) with Product Category as reference characteristic returns no aggregation if there are multiple sales revenue records for each product category. Otherwise, returns the sales revenue of each product category. |
| No aggregation if more than one value arises [NO2] | Return no aggregation if more than one value arises for each combination of reference characteristics. Otherwise, return the value that arises. | NO2 (Sales Revenue) with Product Category as reference characteristic returns no aggregation if there are multiple different sales revenue values for each product category. Otherwise, returns the sales revenue value of each product category. |
| Standard Deviation [STD] | Calculate the standard deviation of all values regardless of their sign or value. | STD (Sales Revenue) returns the standard deviation of sales revenue. |
| Variance [VAR] | Calculate the variance of all values regardless of their sign or value. | VAR (Sales Revenue) returns the variance of sales revenue. |
How to Create Calculated Key Figures with Exception Aggregation in SAP BW/4HANA?
To create calculated key figures with exception aggregation in SAP BW/4HANA, you need to follow these steps:
- Open the BEx Query Designer tool and create a new query or open an existing one.
- In the Rows or Columns panel, right-click on Key Figures and select New Formula.
- In the Formula Editor window, enter a name and description for your calculated key figure.
- In the Formula field, enter your formula using existing key figures as operands. You can use parentheses to group expressions and change the order of operations.
- In the Aggregation tab, select the exception aggregation method that you want to apply to your calculated key figure from the drop-down list.
- In the Reference Characteristic field, select the reference characteristic or dimension that you want to use for grouping the data before applying the exception aggregation method from the drop-down list.
- Click on Check to validate your formula and click on OK to save it.
- Drag and drop your calculated key figure from Key Figures to Rows or Columns as desired.
- Save and execute your query to see the results of your calculated key figure.
Examples of Calculated Key Figures with Exception Aggregation in SAP BW/4HANA
Here are some examples of calculated key figures with exception aggregation in SAP BW/4HANA:
- Average Revenue per Product: This calculated key figure shows the average revenue per product category excluding zero values. The formula is:
Sales Revenue / CN0 (Sales Revenue). The exception aggregation method is:Average of All Values < > 0 [AV0]. The reference characteristic is:Product Category. - Revenue Growth Rate: This calculated key figure shows the percentage change in revenue from the previous month. The formula is:
(Sales Revenue - LAST (Sales Revenue)) / LAST (Sales Revenue) * 100. The exception aggregation method is:Total [SUM]. The reference characteristic is:Month. - Profit Margin Variance: This calculated key figure shows the variance of profit margin among all product categories. The formula is:
(Profit / Sales Revenue) * 100. The exception aggregation method is:Variance [VAR]. The reference characteristic is:Product Category.
Conclusion
In this blog post, we have learned how to create calculated key figures with exception aggregation in SAP BW/4HANA. Exception aggregation is a way of defining a specific aggregation method for a calculated key figure based on a reference characteristic or dimension. By using calculated key figures with exception aggregation, you can create more complex and meaningful KPIs for your business.
We hope you found this blog post helpful and informative. If you have any questions or feedback, please leave a comment below.
Disclaimer: This content is generated by AI.