Nikhil Gangadharappa

November 29, 2024

Understanding the Power of Cumulative Totals in Data Analysis

Introduction

In the world of data analysis, one concept often overlooked but incredibly impactful is Cumulative totals. Whether you’re analyzing financial trends, sales growth, or operational efficiencies, running totals provide a cumulative view of your data, helping to uncover trends and patterns that static snapshots might miss.

A running total is a cumulative sum that keeps track of the ongoing sum of a series of numbers or values. It allows you to see the total value at each step as you progress through the data. This concept is widely used in various fields such as finance, data analysis, and programming.

In this post let’s discuss running/cumulative totals, definition and purpose, their impact and how to build them in different analytics tools.

What exactly are running/ cumulative totals?

Cumulative totals (also known as running totals) are a way of summing up a sequence of numbers in a dataset by progressively adding values as you move through the data.
Essentially, they show the total accumulation of values over time or across a defined sequence, Generally set of dates.

How Do They Work?

Instead of looking at standalone monthly figures (e.g., January: $5,000, February: $6,000 and so on), running totals keep a cumulative tally:

January: $5,000
February: $5,000 + $6,000 = $11,000
March: $11,000 + $7,000 = $18,000

By the end, you see how the total sales have built up over time, providing a clear view of growth and patterns.

How are Cumulative totals useful?

Track Progress Over Time

Shows how values build up over time, providing a clear picture of growth or decline.

Example: In financial reporting, cumulative revenue shows how much has been earned year-to-date (YTD), helping teams monitor progress toward annual goals.

Identify Trends and Patterns

Helps highlight underlying trends or irregularities in data that might be missed when looking at individual values.

Example: Cumulative sales data can reveal if sales are accelerating or slowing down, enabling proactive adjustments.

Simplify Data for Stakeholders

Transforms granular data into a simplified, easy-to-understand metric.

Example: Instead of showing daily web traffic, a cumulative total can show how a campaign contributes to overall engagement.

Measure Achievements Against Targets

Enables a comparison of actual results with predefined goals over time.

Example: Fundraisers use cumulative donation totals to gauge how close they are to achieving their target.

Improve Forecasting and Planning

Offers a basis for predicting future outcomes by analyzing growth rates and patterns.

Example: Analyzing cumulative expenses over months helps forecast whether a project will stay within budget.

Monitor Efficiency

Tracks performance metrics and ensures teams remain on course.

Example: In manufacturing, cumulative production totals help measure whether daily outputs are sufficient to meet deadlines.

Different methods to Calculate Cumulative Total

Simple Cumulative Sum

This is the most straightforward type, where each value is added to the previous total.

How It Works:
Each value in a sequence is summed with all preceding values.

Formula (Excel):
=SUM($B$2:B2)
(Assuming column B contains the values, and the first cumulative sum starts at row 2)

Example:
Values: 10, 20, 30 → Cumulative Sum: 10, 30, 60

Rolling Cumulative Sum

A cumulative sum calculated over a fixed window, like the last 30 days or last 7 transactions.

How It Works:
The sum is calculated only for the most recent values within a specific range or time frame.

Formula (Excel, 30-day rolling):
=SUMIFS(B:B, A:A, “>=” & A2-29, A:A, “<=” & A2)
(Assuming dates in column A &values in column B)

Example:
Daily sales: 10, 20, 30, 40 (last 3 days) → Cumulative Sum: 10, 30, 60 (for last 3 days)

Conditional Cumulative Sum

The sum is calculated only for values that meet specific conditions, such as a certain category or time period.

How It Works:
A filter is applied to sum only qualifying values.

Formula (Excel):
=SUMIF(CategoryRange, “Condition”, ValueRange)

Example:
Sales data filtered for “Region A” → Sum sales only from Region A.

Practical Applications of Running Totals

Financial Reporting

Use Case: Year-to-Date (YTD) Revenue or Expenses Tracking
Companies use running totals to measure revenue, expenses, or profit up to the current date.

Example:

Monthly Revenue: Jan: $10k, Feb: $15k, Mar: $20k.
YTD Revenue: Jan: $10k, Feb: $25k, Mar: $45k.

Purpose: Helps stakeholders track whether the company is on pace to meet annual targets.

Inventory Management

Use Case: Stock Level Monitoring
Retailers and manufacturers use running totals to track inventory levels after shipments, purchases, or sales.

Example:

Starting Stock: 500 items.
Sales: Day 1: 50, Day 2: 100.
Running Total: Day 1: 450, Day 2: 350.

Purpose: Avoids stockouts or overstocking by providing real-time insights.

Sales Performance Tracking

Use Case: Cumulative Sales Monitoring
Sales teams monitor cumulative totals to assess performance over time or during a campaign.

Example:

Weekly Sales: Week 1: $5k, Week 2: $7k.
Running Total: $12k by Week 2.

Purpose: Tracks progress toward quotas and identifies whether adjustments are needed.

Project Management

Use Case: Budget Tracking
Teams use running totals to track cumulative spending against project budgets.

Example:

Budget: $100k.
Running Expenses: Week 1: $20k, Week 2: $30k.
Remaining Budget: $50k.

Purpose: Ensures projects stay on budget.

Marketing Campaign Analysis

Use Case: Cumulative Engagement Metrics
Marketers track cumulative metrics like impressions, clicks, and conversions.

Example:

- Ad Campaign Clicks: Day 1: 500, Day 2: 800.
- Running Total: Day 1: 500, Day 2: 1,300.

Purpose: Analyzes performance trends and helps optimize campaigns.

Operational Efficiency

Use Case: Production Output Monitoring
Factories use running totals to track the number of units produced over time.

Example:

Daily Production: Day 1: 100 units, Day 2: 150 units.
Running Total: 250 units.

Purpose: Identifies whether production is on track to meet deadlines.

Calculating Cumulative Totals in different Analytical Tools

Date	Category	Revenue	Simple C-Sum	Rolling C-Sum	Conditional C-Sum
1-Sep	Appliances	4,784	4,784	4,784	4,784
10-Sep	Travel	6,012	10,796	10,796	6,012
25-Sep	Beauty	5,319	16,115	16,115	5,319
5-Oct	Appliances	9,414	25,529	20,745	14,198
25-Oct	Travel	3,694	29,223	13,108	9,706
30-Oct	Beauty	8,425	37,648	21,533	13,744
5-Nov	Appliances	3,882	41,530	16,001	18,080
12-Nov	Travel	1,950	43,480	17,951	11,656
28-Nov	Beauty	7,179	50,659	21,436	20,923
1-Dec	Appliances	6,319	56,978	19,330	24,399

Excel

Creating a Cumulative Total (Running Total) in Excel is simple and can be done using formulas. Here’s a step-by-step guide and Code :

Arrange Your Data

Make sure your data is structured in a table format:

Column A: Dates or sequence numbers.
Column B: Values you want to calculate the cumulative total for.

Simple Cumulative Sum

=SUM($B$2:B2)

Rolling Cumulative Sum

=SUMIFS(B:B, A:A, ">=" & A2-29, A:A, "<=" & A2)

Conditional Cumulative Sum

=IF($B2="Appliances", SUMIFS($C$2:C2, $B$2:B2, "Appliances"), IF($B2="Travel", SUMIFS($C$2:C2, $B$2:B2, "Travel"),IF($B2="Beauty", SUMIFS($C$2:C2, $B$2:B2, "Beauty"),0)))

SQL

Creating a Cumulative Total (Running Total) in SQL can be done using Sum with Window Partition

Consider data in Table SalesData with the below fields

Date : Dates of the records. [Date]
Category : Category you want the cumulative total for. [VARCHAR]
Revenue : Values you want to calculate the cumulative total for. [INT/FLOAT/DECIMAL]

Simple Cumulative Sum

SELECT 
	Date,
	Revenue,
	SUM(Revenue) OVER (ORDER BY Date) AS [Simple Cumulative Sum] 
FROM SalesData
ORDER BY 
	Date;

Rolling Cumulative Sum

SELECT Date,
       Revenue,
       SUM(Revenue) OVER (ORDER BY Date 
							ROWS BETWEEN 30 PRECEDING AND CURRENT ROW
						) as [Rolling Cumulative Sum]
FROM SalesData
ORDER BY 
	Date;

Conditional Cumulative Sum

SELECT 
	Date,
	Category,
	Revenue,
SUM(amount) OVER (	PARTITION BY Category 
					ORDER BY Date
				) AS [Conditional Cumulative Sum]
FROM SalesData
ORDER BY Date;

Power BI DAX

Creating a Cumulative Total (Running Total) in Power BI can be done using measures. This provides the user with dynamically changing running totals with slicer/filter integration.

Consider data in Table SalesData with the below fields

Date : Dates of the records. [Date]
Category : Category you want the cumulative total for. [VARCHAR]
Revenue : Values you want to calculate the cumulative total for. [INT/FLOAT/DECIMAL]

Simple Cumulative Sum

CALCULATE(
    SUM('SalesData'[ Revenue ]),
    FILTER(
        ALL('SalesData'[Date]),
        'SalesData'[Date] <= MAX('SalesData'[Date])
    )
)

Rolling Cumulative Sum

= CALCULATE(SUM('SalesData'[ Revenue ]),
		FILTER(ALL('SalesData'[Date]),
				'SalesData'[Date] > MAX('SalesData'[Date]) - 30 && 
					'SalesData'[Date] <= MAX('SalesData'[Date])))

Conditional Cumulative Sum

= CALCULATE(
    SUM('SalesData'[ Revenue ]),
    FILTER(
        ALL('SalesData'),
        'SalesData'[Category] = MAX('SalesData'[Category]) &&
        'SalesData'[Date] <= MAX('SalesData'[Date])
    )
)

Power BI can offer much more than what the above code can accomplish. The DAX code are for measure. Additionally,

Columns can be created which can produce the same results.

Power Query can be used to transform and load data.

Category can be used in filters to slice the data as required.

Python With Pands

Python is definitely the language of data science and provides a huge array of libraries that can achieve cumulative sum.

In this particular example, I’m achieving the results using Pandas dataframe with sample data as in the code snippet.

Use Free Python Compiler : https://www.programiz.com/python-programming/online-compiler/

Simple Cumulative Sum

import pandas as pd

# Sample data
data = {
    "Date": ["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04"],
    "Sales": [100, 200, 150, 300]
}

# Create DataFrame
df = pd.DataFrame(data)

# Convert Date column to datetime
df["Date"] = pd.to_datetime(df["Date"])

# Calculate cumulative sum
df["Cumulative_Sales"] = df["Sales"].cumsum()

print(df)

Rolling Cumulative Sum

import pandas as pd

# Sample data
data = {
    "Date": ["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04", "2024-01-10", 
             "2024-01-15", "2024-01-20", "2024-01-25", "2024-01-30", "2024-02-01"],
    "Sales": [100, 200, 150, 300, 250, 400, 350, 500, 450, 600]
}

# Create DataFrame
df = pd.DataFrame(data)

# Convert Date column to datetime
df["Date"] = pd.to_datetime(df["Date"])

# Sort the DataFrame by Date
df = df.sort_values(by="Date")

# Set the Date column as the index (optional, but useful for time-series data)
df.set_index("Date", inplace=True)

# Calculate a 30-day rolling cumulative sum
df["30_Day_Rolling_Sum"] = df["Sales"].rolling(window="30D").sum()

print(df)

Conditional Cumulative Sum

import pandas as pd

# Sample data
data = {
    "Date": ["2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04",
             "2024-01-01", "2024-01-02", "2024-01-03", "2024-01-04"],
    "Category": ["A", "A", "A", "A", "B", "B", "B", "B"],
    "Sales": [100, 200, 150, 300, 50, 80, 40, 100]
}

# Create DataFrame
df = pd.DataFrame(data)

# Convert Date column to datetime
df["Date"] = pd.to_datetime(df["Date"])

# Sort values by Date (and optionally by Category for safety)
df = df.sort_values(by=["Category", "Date"])

# Calculate cumulative sum grouped by Category
df["Cumulative_Sales"] = df.groupby("Category")["Sales"].cumsum()

print(df)

Summary

Cumulative totals or running totals, provide insights into growth and trends over time or sequences. They transform static snapshots into dynamic visuals, revealing patterns often missed in standalone figures. By tracking progress, identifying trends, and simplifying data for stakeholders, cumulative totals become invaluable for decision-making across various fields like finance, marketing, and project management.

The type of cumulative total to use depends on the specific use case. A simple cumulative sum shows the running tally of values, while rolling or conditional sums focus on recent windows or filtered conditions, respectively. Most analytics tools, such as Excel, Power BI, Tableau, Python, and SQL, offer multiple methods to calculate these totals, ensuring flexibility for different scenarios and datasets.

Visualizing cumulative totals in tools like Power BI or Tableau makes identifying patterns and trends significantly easier. Such tools turn raw numbers into comprehensible graphics, enabling clearer storytelling and effective communication of insights to stakeholders.

Contact Me

Address

Al Barsha 1, Dubai
United Arab Emirates

Email Us

Email :Nikhilgangadhar@nikhilgangadhar.com
Gmail : Nikhilgangadharappa@gmail.com

Let's Talk

Phone : +971 502884868
Phone : +971 555270292