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.
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.
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.
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%.
A similar concept is used for distribution using self-reference with data extension.
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.
The situation is different if there are reference values for distribution based on self-reference.
In this case, these are taken into account in the distribution.
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.
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.
If, on the other hand, values are available, the values themselves are displayed.
This column with formula can now be used as a reference for distribution.
Initially, no values are available.
The value entered in the sum is then distributed equally.
Let us now look at the other case, where reference values are available.
In this case, the distribution is based on the existing values.
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.