Subtotals by dimension in the last position

[Former Member]

I have 4 dimension in the report. Dim1 Dim2 Dim3 Dim4 with dynamic expansion. I need a subtotal rows at the bottom for all members of Dim4 (PROJECT)

See the sample report image. Is it possible to create subtotals for dimension in the final position of a axes.

I have already tried to create a local member =SUM(EPMSELECTEDMEMBER(PROJECT)) with Insert After Row axis option but did not work.

I cannot move the Dim4 to any other position.

Thank for your replies.

[Former Member · Accepted solution]

Hi,

You can insert a local member 'After Row axis' in your report.Assuming that you have fixed no. of columns in the report.

Thanks,

Varsha

[Former Member · Accepted solution]

Hi Varsha,

I don't think your formula does a subtotal only on PROJECT (dim4) as shown in the bottom of my original image. It will create a total row at the bottom. Also not that the Row expansion is dynamic.

[Former Member · Accepted solution]

Hi Bobby,

Apologies as I misinterpreted your question.

As Andy suggested, you can group the project dimension to reflect the totals for each project.

Another option if feasible would be to create another EPM report below with only the Project Dimension in Rows and TIME dimension in column.

Thanks,

Varsha Nalkur

[Former Member · Accepted solution]

Since Project is the last dimension on row axis grouping is not possible.

[former_member186338 · Accepted solution]

You can try to use SUMIF in the local member...

[Former Member · Accepted solution]

Actually, that is the first thing I tried. But, how to make the parameters dynamic ?. for example a local member =SUMIF(A1:A20,"Proj-15467",C1:C20) is always applying the formula for the 20 rows only. If the row expansion increases or decreases the formula is not adjusted.

[Former Member · Accepted solution]

Hi Bobby,

To make it dynamic you need to use excel formulas, read up on index, offset key words.

Andy

[former_member186338 · Accepted solution]

Something like:

Vadim

[Former Member · Accepted solution]

Hi Vadim, That worked like a charm. Thanks for your help.

[former_member186338 · Accepted solution]

Then - close the discussion

[Former Member]

Hi Bobby,

You can create a grouping sub total from EPM report editor.

If you want everything at the bottom report you can hide the lines and ref to the above.

Andy

[Former Member]

Hi Andy, Thanks for your reply. Could you please elaborate with some steps. I want to show both the report and the subtotals in the format above, not just the subtotals. I don't want to group the report by Project in the PROJECT dim member selection.