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