cancel
Showing results for 
Search instead for 
Did you mean: 

How to dynamically sum rows?

Former Member
0 Kudos

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.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
Active Contributor
0 Kudos

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
Explorer
0 Kudos

Your attitude to help others is highly appreciated!

Former Member
0 Kudos

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

I really appreciate your help.

Answers (1)

Answers (1)

former_member186338
Active Contributor
0 Kudos

Hi Vaibhav,

Unfortunately not clear:

1. Provide a report screenshot, much better than hundreds of words...

2. The requirement to have sum of each 8ths row can be changed to sum only cells with some particular member in row...

Vadim

Former Member
0 Kudos

Hi Vadim,

I am attaching the screenshot.

The first 8 rows of the report are for every member of profitctr. Here I have selected only 1 (Cisco Pengo). Now I need to have the total for every row at the bottom for which I have made localmembers.

The number of members of dimension PROFITCTR are dynamic so I am facing problems in summing them. Hope I'm clear now.

former_member186338
Active Contributor
0 Kudos

Hi Vaibhav,

Look on my proposal to use SUMIF in local member formula. It's possible to identify the row to sum using some unique member:

B.R. Vadim

P.S. Karsten case was much more complex, in you case it will work fine.

former_member186338
Active Contributor
0 Kudos

P.P.S. On the screenshot I am unable to identify what 8 rows you are talking about... Can you clearly explain what you want to sum?

Former Member
0 Kudos

Hi Vadim,

Thanks for reply. I will try ur method. The rows I'm talking about are the 4 rows corresponding to member of Account dimension "GL revenue"(Actual, Budgeted, variance # and variance%) and the 4 rows corresponding to member of Account dimension "Number of Stages"(Actual, Budgeted, variance # and variance%) i.e. TOP 8 rows of the report excluding the header row.

Cisco Pengo is a member of Dimension "PROFITCTR" and hence every member of PROFITCTR will have 8 rows to it as Cisco Pengo has. Here, in the screenshot, I have only one member of PROFITCTR selected for demo, I will have more members to it in Actual report.

Now the requirement is to sum Total GL Revenue Estimated, Total GL Revenue Budgeted and so on.

So, if Cisco Pengo -GL Revenue-Estimated is the first row and I have another PROFITCTR member say DALLAS in the report, then its GL Revenue-Estimated would be 9th row in the report and it will also have other 7 rows as Cisco Pengo has.

So, my sum for Total GL Revenue Estimated should show on the 17th Row and it should add 1st and 9th row to get the result.

Hope I'm clear.

former_member186338
Active Contributor
0 Kudos

Sorry, absolutely not clear!

Show everything on the screenshot...

Vadim

Former Member
0 Kudos

I hope it should be clear now what I'm trying to do.

The number of profitctr members are variable(A and L at this time) , so the TOTAL will have to adjust accordingly.

former_member186338
Active Contributor
0 Kudos

Just to confirm - in yellow:

Vadim

Former Member
0 Kudos

Hi Vadim

Sorry for late reply. Was traveling.

Anyways you have got it right. The cells in yellow are exactly how I want it in my report.

Vaibhav

Soumendu
Explorer
0 Kudos

Thanks to Vadim for his approach to get the requirement clear!!!! It is highly appreciated!!

It can be met by adding local members in the report editor options.

Regards,
Soumendu