Flexible Disaggregation in SAP Business Planning and Consolidation

Chris

Written By: Chris - 01 February 2024
(updated on: 05 July 2024)

The ability to aggregate and disaggregate large volumes of data with high performance is one of the advantages of the HANA database. This enables planning at different levels in SAP Business Planning and Consolidation (BPC) and SAP Analytics Cloud (SAC) applications. For example, planners can enter values on a hierarchy node and these are distributed to the underlying leafs.

There are several options for carrying out disaggregation in BPC. The distribution can be done as an equal distribution, using another key figure or using self-reference. There is also the option of file extension, which performs an equal distribution based on existing values in another key figure if no data is available for the distribution based on self-reference. I will go into these options in more detail below and illustrate them with examples.

However, what if a distribution based on self-reference is generally desired, but if no reference data is available for the disaggregation, the values should be distributed equally. While this behavior is standard in SAC, BPC requires additional customizing. In this article, I will showcase a possible solution.

Let's take a look at an example. The following data is available as a starting point. In 2021, values are available for company codes 1000 and 3000. In 2022 for company code 2000. Now we want to enter the total value for 2023. Let's say 300 EUR.

001-start_ SAP Business Planning and Consolidation

If we now try to enter 300 in the Total result line, the error message appears stating that the value cannot be distributed because no reference values are available.

There are the following possible solutions. Firstly, we could carry out distribution using another key figure that contains reference values.

002-reference-2021_ SAP Business Planning and Consolidation

If we carry out the distribution based on 2021, the result is as follows. The values are distributed based on the ratios in 2021. One quarter is written to company code 1000 and three quarters to company code 3000. In other words, 25 and 75 percent respectively.

003-distribution-2021

The distribution using the year 2022 works in the same way. The entire amount is written to company code 2000. The distribution corresponds to 100%.

004-distribution-2022_ SAP Business Planning and Consolidation

A similar concept is used for distribution using self-reference with data extension.

005-data-extension_ SAP Business Planning and Consolidation

For example, you can add the actual values when disaggregating the planned values for sales. If no plan data is available, the actual data (e.g. from the previous year) can be used as a reference for the distribution. However, the weighting factors for the disaggregation come from the plan values. If no plan values are available, the values are distributed equally among the existing company codes.

006-data-extension-2021_ SAP Business Planning and Consolidation

The situation is different if there are reference values for distribution based on self-reference.

007-reference-distribution_ SAP Business Planning and Consolidation

In this case, these are taken into account in the distribution.

007-reference-distribution-result_ SAP Business Planning and Consolidation

This comes close to the desired requirements, but does not yet cover them completely. If we remember, the values should be distributed equally if no reference values are available. However, if we use the year 2021 as a data extension, the year 2023 remains empty.


SAP Planning tools compared -
Dowload the Whitepaper here!

Neuer Call-to-Action


To cover the requirements completely, we can use a trick. We define an additional column using the following formula:

NODIM(IF("[0AMOUNT] Amount 2023" <> 0 == 0; 0; "[0AMOUNT] Amount 2023"))

First we check whether the amount is not equal to 0.

"[0AMOUNT] Amount 2023" <> 0

This is a Boolean formula that returns either the value 0 or 1. If the Amount is 0 or empty, a 0 is output. If the Amount is not equal to 0, a 1 is output.

We then check whether the result of the Boolean formula equals 0.

"[0AMOUNT] Amount 2023" <> 0 == 0

In this case, the formula outputs 0. Otherwise, the amount itself is displayed.

NODIM(IF("[0AMOUNT] Amount 2023" <> 0 == 0; 0; "[0AMOUNT] Amount 2023"))

Let's check how the formula works. If there are no values, a 0 is output for all company codes.

011-boolean-initial_ SAP Business Planning and Consolidation

If, on the other hand, values are available, the values themselves are displayed.

013-boolean-reference-data_ SAP Business Planning and Consolidation

This column with formula can now be used as a reference for distribution.

010-reference-boolean_ SAP Business Planning and Consolidation

Initially, no values are available.

011-boolean-initial_ SAP Business Planning and Consolidation

The value entered in the sum is then distributed equally.

012-boolean-equal-distribution_ SAP Business Planning and Consolidation

Let us now look at the other case, where reference values are available.

013-boolean-reference-data_ SAP Business Planning and Consolidation

In this case, the distribution is based on the existing values.

014-boolean-reference-data-distribution_ SAP Business Planning and Consolidation

We hope that this trick will help you and that you will find many areas of application for it. As you can see, with SAP BPC you have a powerful tool at your disposal with which you can implement the most difficult requirements. Do you need support with your planning solutions? Please do not hesitate to contact us.

Learn all about SAP BPC

Topics: SAP Planning

Share article