Skip to Content
0

EPM - SAP IBP - Subtotals & Total of Subtotals (knowing there are filters in planning view)

Jun 21, 2017 at 03:39 PM

322

avatar image
Former Member

total-of-subtotals-when-filter.jpg

Hi there,

I'm actually helping a client building his Planning Views (IBP excel) and I have an issue with creating a Local Member (Total of Subtotals) that would sum another local member (Subtotals) that I did.

Business wise, I have a Local Member that sums values by "Director" for one specific KeyFigure out of 2 (see KeyFigure 2 in attached image).To do that we entered the formula for the Subtotals in "EPM Member Sorting & Grouping" Section on Dimension "DIRECTOR" as follow:

=SUMIF(OFFSET(EPM_SAME_PROPERTY(DIRECTOR,47932f46-b7b1-4207-b693-d9f7a18aaaed),0,-COLUMN()+10),"KeyFigure2",EPM_SAME_PROPERTY(DIRECTOR,47932f46-b7b1-4207-b693-d9f7a18aaaed))

This formula works perfectly and allow us to subtotal only values from KeyFigure 2, or else it would sum values of KeyFigure 1 and 2.

First Question: Is there another way to do that? Instead of going through "Member Sorting & Grouping" ...Is there a formula that would work in regular Local Member section that would sum values only for a specific Key Figure per Director (like a subtotal)?

Second Question: How can I do a Total of those Subtotals per PLANT, meaning sum the different Subtotals per Director per Plant. See image to understand better my question?

N.B: I tried doing it the simpliest way, which is having a Total per PLANT but it gave me a value that doesn't match what I see in the planning view.....maybe because there are filters! For exemple the sum of Subtotals should be 300 000, I was seing 350 000...I assume it was summing also the rest of the values even if they are filtered ?!?

Thank you very much for your help!

----------------------------------------------------------------------------------------------------------------

=> Or else please give me the best/simpliest way to have Subtotals of a specific KeyFigure per a specific Dimension/Attribut in a IBP planning view. (ex: sum of KeyFigure 2 per Director). Knowing there's multiple KeyFigures showing in the Planning View.

+

the total of those subtotals (ex: sum of (sum of Key Figure 2 per Director)).


10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Vadim Kalinin Jun 21, 2017 at 05:38 PM
0

May be instead of local members to sum by property it's better to use customer parent node?

Like:

Plant1 Stephan P. CustomerTotal Key Figure 2

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Vadim,

Thank you for the quick answer, I tried but didn't succeed.

I went to IBP -> EPM Edit Report -> Local Member -> Formula and entered the formula you mentioned but it doesn't work.

Is there dots missing in the formula? like Dimension1.Dimension2.Dimension3Total.Key Figure2?

Or is it spaces between like you wrote? In other terms, how exactly should I write it down to make it work?

And last, the formula you gave me works only for Stephan P. right? Because I would need a dynamic formula that calculates sums for each of the director......

Spasiba!

0

I am not talking about local member!

I am talking about member selection

0
Former Member

member-selector-epm.jpg

See attached picture.

That's the only other place in IBP_EPM_Add_In where I can add formulas.

Is that what you're talking about when you say member selection?

If yes, I tried "Plant1 Stephan P. CustomerTotal Key Figure 2" there and it doesn't work.

In both case, "Plant1 Stephan P. CustomerTotal Key Figure 2" doesn't look like a formula to me?!? Could you detail your answer a little please? that would help us a lot!

Thank you Vadim for your help. Much appreciated!

0
Former Member

Not best answer in my case!

I am not satisfied with the answer, since it doesn't seem to take in consideration my concerns.

Plus, there's a difference between EPM and EPM in IBP. IBP seems to be reacting weirdly to some EPM functions.

Vadim if you're not able to help me, could you lead me to someone who can?

Thank you for your help.

0

Sorry, It's a public forum and I am not working for SAP to provide you the service like: "Vadim if you're not able to help me, could you lead me to someone who can". Absolutely strange wording!

Unfortunately you explanation of the issue is not clear and I can't help you more. May be it's the reason that nobody else answered your question.

0
Former Member

It was to make you reply faster Vadim, since I have been without reply from you since my last comment in June.

It worked.

I'll communicate with SAP then!

Hope our paths will cross again.

No hard feelings.

Take care!

0

Just a recommendation: clearly explain what do you want to achieve using some sample report screenshot. Not the way to achieve it :)

0