Cumulative Total within SAC Model

[djk13]

Hello Experts,

I am looking to create a running total for every material in my model and then break those totals down into percentages per functional area per month. I would like this calculation to be stored in the model, as opposed to a story, if possible. I am currently using SAC to S/4 HANA cloud connection, import model - not live.

For example, I have material X and material Y. 15 of Material X were consumed in functional area 1 in Jan, and 10 were consumed in functional area 2, but only 5 of those in January. So, 75% vs 25% would be my expected split for Material X in Jan.

What is the easiest way to accomplish this?

Thank you!

[N1kh1l]

djkoenig

Please show a report from SAC showing the consumption numbers for both materials across FA. Can I assume both FA and Material are dimensions in your model?

Nikhil

[N1kh1l · Accepted solution]

djkoenig

Assuming that both Material and FA are dimensions. You can try something like below.

Create a calculated Account/Measure called [Quantity Aggr] for aggregating the Quantity across Sales org ( in your case FA) .

Below example is based on an Account Model with Account member [QUANTITY]. The same can be replicated in Measure based Model using calculated Measures.

Quantity Aggr: Quantity aggregated by sales Org

LOOKUP([QUANTITY],,[d/SAP_CEP_SALESORG])

Consumption Split:

[QUANTITY]/[Quantity Aggr]

Output:

Hope this helps !!

Please upvote/accept if this helps.

Nikhil

[djk13 · Accepted solution]

Hello nikhil_1486,

Thanks for taking the time to answer! Your explanation seems to make sense. It just does not seem to work on my end. I, unfortunately, am running into this issue when I try to add the calculation:

And I don't think it is intended to be a calculation in the modeler itself.

So, if it can't be used in a story (tried both optimized and classic) or the modeler, where do I utilize it? Apologies if I am missing something simple!

[N1kh1l · Accepted solution]

djkoenig

You had mentioned that you want the calculation to be kind of available in model itself and not local to one story so My solution was based on creating the calculated member in Account dimension (classic model) / calculated measure in new measure based model. If you want to do it a story level (local to the story in which created), Try below.

Create a Restricted Measure as below. For you Sales Org will become FA. For Values select the top node (if hierarchy exist) or select all members. Also enable constant selection with FA.

Create a calculated Measure:

["SAP_CEP_SALES_PLANNING":QUANTITY]/[#Quantity Aggr]<br>

Output: Same as what was in previous approach.

Hope this helps !!

Please upvote/accept if this helps

Nikhil

[djk13 · Accepted solution]

Nevermind nikhil_1486. I totally forgot about the post model creation calculations section. I'll leave my very silly question above for any future newbies. You nailed it!

Now, I can just divide in my story 🙂

[djk13 · Accepted solution]

Hello again nikhil_1486,

You have been super helpful on this topic, and I do have a follow-up question.

Can you use LOOKUP on a function that you have created (i.e. absolute value of a quantity)?

In the formula I reference Abs Quantity which is just the absolute value of the quantity. In the case of product 2100 I imagined it would be 128508.86 + 80370.00 for my aggregated quantity of 208878.86. However, it is still using -80370 and I get an aggregated value of 48138.86 instead.

What have I missed?

Thanks!

[N1kh1l · Accepted solution]

djkoenig

LOOKUP of Abs Qty might still end up pulling -ve result. Try this instead

Create a calculated Measure [Abs Quantity] = ABS([QUANTITY] // which you have already done

Create a calculated measure [Qty Abs Aggr] using [Abs Quantity] with exception aggregation Sum and Exception Aggregation Dimension as FA

Change Quantity Aggr to below

LOOKUP([Qty Abs Aggr],,[d/Functional_Area_Name])

Change Consumption to

[Abs Quantity]/[Quantity Aggr]<br>

Output:

Nikhil

[djk13 · Accepted solution]

Wow! I really do appreciate the detail in your responses. That works just fine. Thanks for guiding me through 🙂

[saurabh_sonawane]

Hi,

You can use grandtotal function and try to get 75% and 25% in the value,

While displaying display functional area in column and in row material X and month

it will show you 75% VS 25%

Thanks,

Saurabh

[N1kh1l]

% Grand Total will not work correctly when you have data for more than one month. It will add all months together when doing the contribution.

Nikhil