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.

 

Pareto

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