How to dynamically sum rows?

[Former Member]

Hi Gurus,

I am facing a problem in my report. The requirement is that I have to sum every 8th row in the report starting from 1st row upto the end of the report.

For eg. I have to sum 1st,9th, 17th row and so on. The total should come after the end of the report.

I am using local members to show "TOTAL" at the end of the report.

The problem that I am facing is that the number of rows are according to the number of members of the PROFITCTR dimension of the row axis.

If we select 1 member of the dimension, then only 1st row sum should show in the bottom. If there are 2 members, then sum of 1st and 9th row should show in 17th row and so on. If I write a simple sum formula adding the cells till the maximum possible members of the dimension, then the sum is coming fine of all members are selected but it comes to 0 if any lesser members are there. I hope I am clear with the requirement.

Kindly help out.

[Former Member · Accepted solution]

Hi Soumendu and Vadim,

I am trying to do by adding local members but I cannot get the formula right. I have tried using sumifs but it is giving an error that Local member formula cannot be applied.

Can you help with the formula which will sum as per requirement.

Thanks

[former_member186338 · Accepted solution]

Hi Vaibhav,

I managed to create the required report using 14 local members and complex SUMIF's with INDIRECT(ADDRESS(...))

In general in local member formula you can use EPMPOSITION(1) to get the first data row number and you can generate the range from the first data row till the current row of local member - ROW().

Result:

Some local members:

To calculate Variance for each title:

Variance # =EPMMEMBER([PLANTYPE].[].[ACTUAL])-EPMMEMBER([PLANTYPE].[].[BUDGET])

Variance % =(EPMMEMBER([PLANTYPE].[].[ACTUAL])-EPMMEMBER([PLANTYPE].[].[BUDGET]))/EPMMEMBER([PLANTYPE].[].[BUDGET])

To create the column with combined INACCT and PLANTYPE:

INACCTandPLANTYPE =EPMMemberProperty(, EPMMemberID(EPMDIM_CURRENT_MEMBER(INACCT)), "ID")&EPMMemberProperty(, EPMMemberID(EPMDIM_CURRENT_MEMBER(PLANTYPE)), "ID")

To calculate Total:

Total 202020202 ACTUAL =SUMIF(INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),4)&":"&ADDRESS(ROW()-1,4)),"202020202ACTUAL",INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Total 202020202 BUDGET =SUMIF(INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),4)&":"&ADDRESS(ROW()-1,4)),"202020202BUDGET",INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Total 202020201 ACTUAL =SUMIF(INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),4)&":"&ADDRESS(ROW()-1,4)),"202020201ACTUAL",INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

Total 202020201 BUDGET =SUMIF(INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),4)&":"&ADDRESS(ROW()-1,4)),"202020201BUDGET",INDIRECT(ADDRESS(ROW(EPMPOSITION(1)),COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))

To calculate total variances:

Total 202020202 Variance # =INDIRECT(ADDRESS(ROW()-2,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Total 202020202 Variance % =INDIRECT(ADDRESS(ROW()-1,COLUMN()))/INDIRECT(ADDRESS(ROW()-2,COLUMN()))

Total 202020201 Variance # =INDIRECT(ADDRESS(ROW()-2,COLUMN()))-INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Total 202020201 Variance % =INDIRECT(ADDRESS(ROW()-1,COLUMN()))/INDIRECT(ADDRESS(ROW()-2,COLUMN()))

And to present row headers:

TITLES =IF(C6="Total 202020202 ACTUAL","Total",IF(A6=A5,"",A6))

INACCT =IF(C6="Total 202020202 ACTUAL","202020202",IF(C6="Total 202020201 ACTUAL","202020201",IF(B6=B5,"",B6)))

PLANTYPE =IF(LEFT(C6,5)="Total",MID(C6,SEARCH(" ",C6,7),255),C6)

In the final report columns A,B,C,D will be hidden.

B.R. Vadim

[Former Member · Accepted solution]

Thanks a lot Vadim,

It seems you had to invest a lot of time and effort to come up with this result.

I'll try it and inform about its working.

Thanks again.

[former_member186338 · Accepted solution]

Not a lot, just some copy paste of the same formulas...

The idea was to use SUMIF with the range from the first report row to the local member row. Also the column with concatenated string with criteria for SUMIF was used.

Definitely it's not a perfect way of creating reports like this, but it's working... I have tested this report adding or removing titles and got correct results.

Vadim

[Soumendu · Accepted solution]

Your attitude to help others is highly appreciated!

[Former Member · Accepted solution]

Thanks a lot Vadim...It is working....

I really appreciate your help.