Skip to Content
avatar image
Former Member

Sum with Local Members in EPM

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 09, 2016 at 05:50 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2016 at 11:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 16, 2016 at 02:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded