Creating Running Totals/Partial Sums in BI Office

by Sandesh Nagaraj , Senior Sales Engineer

What is Running Total/Partial Sum?

A running total is the summation of a column of values which is updated each time a new row is added to the grid or table, by adding the value of the new value to the previous running total. Another term for it is partial sum. The purposes of a running total are twofold. First, it allows the total to be stated at any point in time without having to sum the entire value each time. Second, it can save having to record the sequence itself, if the particular numbers are not individually important.

There are two ways to do this in BI Office: Using the Pareto option under Advanced Algorithms or creating a Running Total measure.



In the first method, make your selection, such as sales across time. Next, choose Pareto from the Analytics tab and select to Add Pareto option.

Choosing Add Pareto Option Under Analytics Tab


You can then edit the Pareto line from the legend to change color and style type to fit your need.

Edited Pareto Line


But what if you would like to see the values of the running total in a grid or adjust the formatting of the line from a line to a bar or area chart? This you can do with a few of the following steps.


Advanced Algorithms

For example, in accounting, the total is carried over to the next column. A total is carried from one column or set of figures to the next.

Below we have sales value at a monthly level.

Chart Showing Monthly Sales Values


Now we need to find out a running total of the sales value at a particular month.


Step 1

Select the Analytics tab in the ribbon and select the Calculation Designer.

Select the Calculation Designer


Step 2

Select Custom Member.

Select Custom Member


Step 3

Select Sum function from the “other Fx” section.

Select Sum 


Step 4

Name the measure Running Total. The first parameter of the sum function is a set. So start from Null to Current Member.

Sum (null: Member, Data Point)


Step 5

Now select Sales Measure in the data point parameter section.

Select Sales Measure


Step 6

Add the new measure to the report.

Add the Measure to the Report


You can also place both items on a single chart, place the running total on a secondary axis, make it a different color, and provide a hash line.

Display on Secondary Axis


Also, you can make the running total an area chart and change the color again. 

Running Total as an Area Chart

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • nice!!

  • Old thread, but slightly modified instructions works for Pyramid 2018 too. I have actually utilised lists, instead of null: for the set I have the first sibling in the list, it works well.

Like2 Follow
  • 3 yrs agoLast active
  • 2Replies
  • 1176Views
  • 4 Following