<img src="https://certify.alexametrics.com/atrk.gif?account=3HHNq1DlQy20Y8" style="display:none" height="1" width="1" alt="">

SAP Analytics
Thought Leadership

Calculating Column Totals Dynamically - SAP BPC

Posted by
Deb Silverman
Deb Silverman
on Wed, Mar 16, 2016 @ 12:03 PM

Need to calculate a grand total dynamically?  Shifting of Local Members giving you a headache?  I previously usedCalculating_Column_Totals_Dynamically_with_SAP_BPC.png Indirect() functions, but I discovered with larger reports this put a drag on reporting performance.  To sum from the first report column to the last report column I was using something like this  =SUM(EPMPOSITION(1):INDIRECT(ADDRESS(ROW(),COLUMN()-1))), which is not considered best practice but I got results!

The SAP definition: “EPMALLMEMBERS is used to perform a calculation (such as sum or average) on all the members displayed in a row or column axis, whether the axis contains one or several dimensions”

HOW?  Here are some examples:

  • You can create a local member with SUM(EPMALLMEMBERS), which also works when there are multiple dimensions in the rows.
  • You can also use it in calculations such as = SUM(EPMALLMEMBERS)+$A$1.
  • Another example is to commit the sum to a specific column =IF(COLUMN()>=15,SUM(EPMALLMEMBERS)+$A$1),"").
  • An example from SAPs documentation for when we are using various periods. To calculate the sum for time periods use SUM(EPMALLMEMBERS(TIME)).

Related post: Formatting in SAP BPC

For tips like these and much more, download our free eBook entitled "Top 10 SAP BPC Reporting Tips"!

 Reporting eBook v3-01.png

Related Articles:

Blog Post: How to Supress Send Dialog Box in SAP BPC

Blog Post: SAP BPC Reporting Tips & Tricks

Blog Post: Using the Power of Visual Basic in EPM 10 Reporting

Blog Post: Tax Reporting Just Got a Whole Lot Tougher! (Thanks to the OECD)

Customer Roundtable Webcast - BPC Performance Issues


Author Bio:

Deborah Silverman has been with Column5 Consulting for almost 5 years as a Senior Consultant. She is one of our most popular blog writers with her decades of software experience. She has assisted in hundreds of implementations and specializes in SAP BPC. 

Deborah Silverman, Senior Consultant, US Team


Topics: Best Practices, Excel, Training, Performance


Recent Posts

Posts by Topic

see all

3  S T E P S

To Enhance Your EPM Performance:

 1.Get Your Ultimate Guide to Improving SAP EPM Performance

ebook 2

 Learn what could be contributing to your poor performance and how to diagnose common problems. Get tips that will empower you and your team to improve the  performance  of your system in order to get the most out of your SAP EPM Investment.

Get the e-Book


2.  Test Your BPC Performance 

Benchmark 2

Get a BPC performance report card and custom report to identify performance issues you may not even know you have.

Test my BPC


  3. Get Your EPM Assessment  


Our assessment delivers a complete, best practice roadmap for you to follow.  We’ll work together to assess what you need to pivot your financial forecasting, planning, reporting and analysis to handle the compressed requirements from the volatile COVID-19 environment. 

New Assessment