# BI Integrated Planning (auto compute components with FIXED TOTALs)

Hi guyz,

I'm wondering if the scenario given below is possible in BI Integrated Planning. If so, how will I do it?

For example, I have four (4) materials with their corresponding volume (shown below).

Material

Volume

(percentage)

Mat A

40

1. 0.2

Mat B

30

1. 0.15

Mat C

60

1. 0.3

Mat D

70

1. 0.35

TOTAL

200

1

Then, I want to increase the volume of Mat B to 45. However, I want my TOTAL to be fixed hence, the volume of the remaining three (3) materials (Mat A, Mat C, and Mat D) should change so that the total of the four (4) materials will still be 200.

Material

NEW VOLUME

(percentage)

Mat A

35

1. 0.175

Mat B

45

1. 0.225

Mat C

55

1. 0.275

Mat D

65

1. 0.325

TOTAL

200

1

So if I want to decrease the volume of Mat D to 35, the volume of Mat A, B, and C will also change to still have a TOTAL of 200.

Material

NEW VOLUME

(percentage)

Mat A

45

1. 0.225

Mat B

55

1. 0.275

Mat C

65

1. 0.325

Mat D

35

1. 0.175

TOTAL

200

1

Is this possible in BI Integrated Planning? How can it be done? Do I need to use any of the Planning Functions?

Thanks a lot for the help!

<<Text removed by moderator>>

Message was edited by: Matthew Billingham

10|10000 characters needed characters exceeded

### Related questions

• Posted on Apr 25, 2013 at 01:01 PM

Hi ,

this is build in functionality, so called inverse formulas and cell locking. In your case you even don't need cell locking but you can use the %GT function and make this input-ready. In this case the system generates the inverse formula automatically. In case you change the key figure value you have to lock the total. In case you change the percentage the total will be locked automatically (since the %GT is always 100% by definition).

Documentation: Read note 1236347 (attached PDF document: SAP_Note_1236347_Attachment_V2_5.pdf).

Regards,

Gregor

10|10000 characters needed characters exceeded
• Posted on Apr 25, 2013 at 07:33 AM

Hello,

You can try you requirement with FOX formula, juyt try this steps

1) Craete two version in cube 1) Actual 2) Plan

2) Copy actual version to plan version with standard copy function

3) Create a query on plan version and let the user increase volume or decrease

4) Create a planning function and compare two version in fox formula

5) Loop through the cube and compare two version and get the change value use IF case from the cube Ex: ZACTUAL-MAT D = 30 and ZPLAN-MATD= 45 and at same time and get number of loop(Say N = N-1)

7) Get the Zdelta Ex = ( 45 - 30 ) / (N=3) i.e Zdelta = 5

8) Once again loop through the cube and compare version(ACtual and PLAn) and use IF case so that if value are same then use ZPLAN-MATD = ZPLAN-MATD - Zdelta in your case ( MAT A :30 = 35 - 5)

Hope you understand the steps

Regards,

Anand Kumar

10|10000 characters needed characters exceeded
• Loed Despuig Former Member

Hi Anand,

Do you have any documents on how to use the FOX Formula? Kindly post here the name of the docs and I will just read it..Because I can't see any related documents about this in the Internet..

By the way, the ACTUAL and PLAN cubes have different data (they can't be the same) hence I won't be able to do the STEP 2 above - Copy actual version to plan version with standard copy function..I can't use the COPY function from ACTUAL to PLAN cube since we are uploading the "true/actual" data in the ACTUAL cube..As you can see in the WORKBOOK above, all the cells are INPUT-READY, no values are used from the ACTUAL cube..So how will I do it if mate if I can't do STEP 2?

I don't really have an idea mate on how to create the codes in FOX Formula..I don't know how to use this { } or the FOREACH statement..If you have any documents about FOX Formula kindly post here the name of the docs then I will just try to do this after reading it..

Thank you!

Loed

• Former Member
Posted on May 02, 2013 at 10:19 AM

Hi Leod,

You can create a custom Planning function type. After that create planning function of that type. And then filter and Planning sequence.

The planning function type will be using the class. You will have to write code in the execute method of the class.

The logic in the class will go like this.

1. First the code will take the sum of all the materials.

2. The changed material will have the value as it is and the difference of total and previous total will be distributed to all the other materials.

*Please note while coding that the planning sequence fetch data based on filters and the they use the concept of delta.

Please revert if you need any more clarification.

Thanks,

Puneet