Calculate Average in EPM excel for SAP IBP

[Former Member]

untitled.png

Hi All,

Can somebody let me know how to achieve below scenario where let say my current month bucket is April-18 and created local member "Avg Att Pct - WIP" so the values should be stored in it is average of KF "Attainment Pct" of last 3 months. So I want to formulate based on EPM function and not on excel cell reference so that even though someone adds new keyfigure in view than there should be no impact on calculation.

[anna_linden · Accepted solution]

Hi Arun,

the EPM Local Members that you should be using would remember for which cells the calculation was set up even though a user enteres a new key figure to the view.

Example:

Product 1 / Location 1 Consensus Demand (Cell K5)

Product 1 / Location 1 Manual Input KF (Cell K6)

Product 1 / Location 1 Local Memer (K5*K6)

User adds new key figure above Consensus Demand

Product 1 / Location 1 New key Figure (Cell K6)

Product 1 / Location 1 Consensus Demand (Cell K6)

Product 1 / Location 1 Manual Input KF (Cell K7)

Product 1 / Location 1 Local Memer (K6*K7) --> automatically adapted.

In your screenshot I however dont see if the calculation that you added is really recognized as an EPM local member. If you click on the name of the local member key figure "Avg Att Pct - WIP", do you see something like = EPMLocalMember("WIP";"001";"000") in the Excel input field on top?

If not, then please enable the local member recognition in IBP --> Advanced --> EPM Sheet Options --> Activate Local Member Recognition.

Best Regards,

Anna