SAC | Calculations based on different levels (hierarchies)

[MSK17]

I have the following problem.

I have different levels in my model. The Material dimension (one row per material) is the lowest level on which most of the calculations should take place.

Now I have a calculation in which I need the total of a material line, for example (there are 5 different material lines), but I also need to perform calculations on the lowest level (for example: how large is the proportion of the individual material ("quantity") in the total material line (number of all materials). I also need to be able to calculate the price per material.

Only materials that fulfill a certain condition must be included in the calculation. I have programmed this condition in Excel, whereby these are now listed with 1 and 0 in a separate column (account). All materials (rows) that have a 1 in this column must be included in the calculation.

To do this, I would have to iterate through the list (material line consisting of all materials) to add up the values, e.g. the price and the quantity of the individual material, if these are marked with a 1.

These calculations must be calculable as accounts and not displayed in a table.

Thank you very much!

[MSK17]

Problem 1: Wenn ich mit der IF Formel arbeite, aggregiert es mit die 1 aller Materialien der ausgewählten Materiallinie.

[MSK17]

Problem 1: When I work with the IF formula, it aggregates the 1's of all materials of the selected material line.

[N1kh1l · Accepted solution]

@MSK17 

You have not mentioned what kind of requirement is this Analytic or Planning. If its a planning use case you can achieve this using variable members in data Action Advanced formula. The kinf of calculation you gave example is of contribution %. You can use %GrandTotal(Quantity) in the calculated measures. For filtering the materials satisfying the conditions, you have to specify the conditions in either attributes of material or hierarchy grouping.

You can check the various formula available in story calculations below.

https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/3b7c99a7a7cd4a29bb1c5...

Br.

Nikhil