Understanding Average Exception Aggregation in SAP Analytics Cloud

[Alecsandra]

Designing calculations is a core aspect of modeling in SAP Analytics Cloud. The platform offers a comprehensive set of functions, aggregations, and disaggregation techniques that allow modelers to implement business logic effectively. Within this toolbox, exception aggregation plays a key role by allowing modelers to control whether a measure is aggregated before or after formula calculations are applied. This blog focuses on average-type exception aggregation, explains how each type behaves, and outlines the scenarios in which each should be applied. 

There are four types of average exception aggregation, each defining a different way to select the members considered in the calculations.

Average

When to use: Choose “Average” when you want to include NULL and zero values in the calculation.

In SAP Analytics Cloud, NULL values can occur for several reasons:  

• They may originate from imported data containing empty records.
• More commonly, NULLs are created implicitly when another measure is booked for the same combination of dimension members. For example, if Measure A is unbooked for a given intersection (Date “2025-01”, Project “PRJ-001”) but Measure B is booked, SAC assigns a NULL to Measure A for that same combination of dimension members.
• They can also arise in calculated measures, such as formulas that return NULL, or a restricted measure used in the story with a filter outside its restriction interval. For example, if a measure is restricted to 2025 but the table shows all years, the non-2025 years will return NULL.

Understanding this behavior is important because NULL values influence how averages are calculated. Under the “Average” exception aggregation, NULLs increase the count of members in the denominator, even though they do not contribute to the sum. As a result, the final average may differ from what is expected, particularly when measures follow different booking patterns.

NULL and UNBOOKED are not the same. UNBOOKED indicates an empty state, where no record exists. NULL, on the other hand, is a technical value used by SAP Analytics Cloud to represent missing values for a given record/dimension combination, such as when a record/row exists because another measure has a booked value for the same combination. This difference can be observed in the fact view under Model → Data Foundation: Unbooked rows are not shown at all, while NULL cells are explicitly displayed as (NULL).

Average excl. NULL

When to use: Choose “Average excl. NULL” when you want to exclude NULL values from the calculation.

This option ensures that only members with booked values contribute to the average. NULL values are ignored, preventing the booked state from another measure influencing the calculation.

Example

The following example illustrates how "Average" and "Average excl. NULL" produce different results. This scenario uses a simplified planning model in which Capacity Hours, Consumed Hours (Avg. incl. Null, 0), and Consumed Hours (Avg. excl. Null) measures are maintained for the same Project-Role combination. 

• Capacity Hours represents the monthly staffing capacity available for the given project/role.
• Consumed Hours (Avg. incl. Null, 0) shows the actual hours worked/booked and is aggregated using the “Average” exception aggregation.
• Consumed Hours (Avg. excl. Null) shows the same underlying consumption data as Consumed Hours (Avg. incl. Null, 0) but is aggregated using “Average excl. NULL”.

Figure 1. Comparison of Consumed Hours aggregated with "Average" and "Average excl. NULL". February is counted under "Average" but excluded under "Average excl. NULL".

Observed results in Q1 (2025):

• Consumed Hours (Avg. incl. Null, 0) divides the total hours from January and March by all three months in the horizon, including Feb (2025), where there is no activity.
• Consumed Hours (Avg. excl. Null) divides the total by only the months with recorded hours, producing the expected results for this scenario.

Why the difference, and what do the empty values in Feb (2025) represent?

As shown in the screenshot below, taken from the planning model's Data Foundation, the empty cells for the Consumed Hours (Avg. incl. Null, 0) and Consumed Hours (Avg. excl. Null) are assigned with NULLs. This happens because Capacity Hours is booked for the same Project-Role-Period combination. As a result: 

• Consumed Hours (Avg. incl. Null, 0) counts Feb (2025)
• Consumed Hours (Avg. excl. Null) ignores Feb (2025)

Figure 2. Viewing NULL values in the Model → Data Foundation.

Which aggregation is correct?

Before deciding, we need to clarify the business intention for aggregating consumed hours:

• Case A: exclude months with no activity
• Case B: include months with no activity

Consumed Hours (Avg. excl. NULL) produces the correct result for Case A, where months without activity should be excluded.

Consumed Hours (Avg. incl. NULL, 0) may appear correct at first glance for Case B, but its result only aligns with expectations in isolated situations rather than consistently. To understand why, look at what happens when we remove Capacity Hours from February (2025). Removing this value also removes February from the display when the table is set to hide unbooked members. In this state, Consumed Hours (Avg. incl. NULL, 0) returns 240, which is the sum of the booked values from January and March divided by two. The key observation is that the Average type no longer considers February, simply because the record is unbooked rather than NULL. In other words, the same measure, the same dimension combination, and the same consumption pattern produce different results depending on whether the data state was NULL or unbooked.

Figure 3. Removing Capacity Hours from February converts the period from NULL to Unbooked. With no record present, "Average" no longer counts February.

Figure 4. Unbooked period disappears from display when Show Unbooked is turned off.

To fulfill Case B correctly and consistently, the appropriate configuration is not “Average” but “Average incl. Unbooked”. This ensures that all periods in the horizon are counted, regardless of whether they are booked, null, or unbooked. See the “Average incl. Unbooked” section of this blog post.

The use of “Average” (i.e., including Null, 0) should therefore be limited to scenarios where the effects of NULL are intentional and understood. When there is no such explicit use case, another type of average exception aggregation should be used.

Below are examples of cases where such dependencies are purposeful, and the presence of a booked measure determines whether a period should be counted in the average.

• If a Subscription Fee is booked for a month, that month must be counted when computing averages such as Monthly API Calls or Active Subscription Usage, even if there is no usage.
• The existence of a Promotion Price or Promotion Flag defines the eligibility of that period. Because the promotion was available to the market, that time window must be counted when calculating averages such as Monthly Sales.

Average excl. 0, NULL

When to use: Choose “Average excl. 0, NULL” when you want to exclude both zero and NULL values from the calculation.

In addition to excluding NULLs, this exception aggregation also skips members booked with a zero. This is helpful when zero should not serve as a valid observation. Typical examples include KPIs where zero indicates “no measurement taken,” rather than a real data point.

Average incl. Unbooked

When to use: Choose “Average incl. Unbooked” when you want the calculation also to consider Unbooked members. Compared to “Average”, this exception aggregation treats any absence of value the same, regardless of whether the cell is technically stored as NULL or is completely unbooked.

This behavior makes “Average incl. Unbooked” suitable for use cases such as period-based KPIs, where the entire time horizon matters. For example, when calculating an average headcount for a year, you may want the denominator to include all twelve months, not just the months where headcount was recorded.

Extending the Example 

To illustrate the behavior more clearly, let’s extend the previous scenario by adding:

• Consumed Hours (Average incl. Unbooked)
• A second project: PRJ-002

Both projects share the booked data pattern, except that PRJ-002 has no Capacity Hours in Feb (2025). Because no measure is booked at that intersection, Feb (2025) is fully Unbooked for PRJ-002.

Figure 5. Extending the example with PRJ-002 demonstrates the difference between NULL and Unbooked. "Average incl. Unbooked" counts all periods, while "Average" includes only periods with a NULL.

What the results show:

• Consumed Hours (Avg. incl. Null) produces different Q1 averages between PRJ-001 and PRJ-002, even though the underlying booked hours are identical. The difference exists because NULL is counted, while Unbooked is not.
• Consumed Hours (Average incl. Unbooked) returns identical Q1 averages across both projects, because the denominator counts all months regardless of whether they contain booked, NULL, or unbooked values.

"Average incl. Unbooked" exception aggregation is the correct choice for Case B (include months with no activity), while "Average" behaves correctly only if those empty periods happen to be represented in the model as NULL rather than unbooked.

Summary

Exception Aggregation in SAP Analytics Cloud is a powerful mechanism that determines how measures roll up across dimensions. While average-type exception aggregation appears simple, its handling of NULL, zero, and unbooked members can change the result in ways that are not always intended.

Use “Average” only when the inclusion of NULL values is intentional and aligned with the business logic. It should not be selected by default. In most planning and reporting scenarios, the other three average-types ("Average excl. NULL", "Average excl. 0, NULL", or "Average incl. Unbooked") provide the expected behavior for the use case. By evaluating how NULLs are generated, whether they should be included in the average, and whether empty periods represent inactivity or a true absence, modelers can choose the appropriate average-type and ensure that their KPIs return results that reflect the intended business logic and that users can trust.