Sum with Local Members in EPM

[Former Member]

Dear Community,

I am trying to build a local member, that will present me the sum of all values of a certain key figure/attribute combination. Currently, the sheet shows the quantity per brand. Additionally, I want to see the total quantity. Could you please tell me if it is possible to calculate this within a local member and if there is an EPM specific formula for this operation?

Thank you very much,

Kind regards,

Katrin

[sourojit_pal · Accepted solution]

Hi Katrin,

I think the target that you are looking to achieve is somewhat aligned with the following screenshot:

The line highlighted in Red is a local member containing the sum of all the Independent Demand in the current worksheet.

This can be achieved by EPM - Report Editor in Template Admin Section under Advanced in your IBP ribbon.

In the Layout tab -> Under Row Dimensions -> Select Brand

In the new pop up, click on Member Sorting and Grouping -> Click on Enable Member Properties -> Select Group By Property -> Select Before Group or After Group option -> Check Create a local member named -> Provide a Name to display -> In Formula section change only: =SUBTOTAL(9 to =SUMIF(<Total range of Key Figures>,<Your Key Figure>

Click ok -> ok

Hope this helps.

Regards,

Sourojit

[Former Member · Accepted solution]

Hi Sourojit,

thank you very much for the detailed explanation - this was indeed what I was looking for. Can you just please help me with one more specification?

For <total range of key figures> and <your key figure> what is the correct way of writing it so that it is recognized by EPM? If my key figures are e.g. in column B and my key figure has the technical name "TOTALQTY" - what would be the correct writing?

Thank you very much in advance,

Katrin

[sourojit_pal · Accepted solution]

Hi Katrin,

Let me assume that the entire set of Key Figures in your fetched data set begins from Row 1 and ends at Row 50 so in your case <total range of key figures> will be $B$1:$B$50 and

<your key figure> is "TOTALQTY"

Hope this helps.

Regards,

Sourojit

[sourojit_pal · Accepted solution]

Hi Katrin,

Can you please close this thread and mark the correct answer in case the problem is resolved..!!

Regards

Sourojit

[robvid · Accepted solution]

The post is a few years old but still valid and it helped me today

Thank you Sourojit

[Former Member]

Thank you Pradeep and Sourojit for your input.

Unfortunately, the constellations did not work with local members in either way as we experienced an issue in the planning view showing all fields on a technical side as "0" - even though they contained for example the word "Total Quantity".

We decided for a simple way and defined new key figures on an aggregated level that can show the sum.

Thanks again for your help!

Katrin

[pradeepmolugu]

HI Katrin,

Please try to enable option Option Local Member Recognition in sheet options

1. Include formula In the Data range first cell.
2. Refresh report the same formula will expanded into all cell of the report.
3. unchecked the Local member recognition after report Refresh.
4. Local member will created in Local Member Tab of Edit Report menu options, after the refresh of report this formula will be created with own description.

Attached screen shots for your reference

.

Regards,

Pradeep