Implementing Running SUM in Power BI Visual Calculation (Part 1)

In this article, we will examine how to implement cumulative sales (Running Total) by brand utilizing the RUNNINGSUM function provided in Power BI Visual Calculations. In this example, we analyze the cumulative sales trend across brands based on consumer electronics sales data.

1. Example Data

The brand names and sales data below are fictional data created solely for illustrative purposes and bear no relation to the actual sales performance of any company.

• Brand: Apple / Total Sales: $25,000
• Brand: Samsung / Total Sales: $13,000
• Brand: Sony / Total Sales: $5,000
• Brand: Xiaomi / Total Sales: $3,500
• Brand: LG / Total Sales: $1,500
• Brand: Huawei / Total Sales: $1,200
• Brand: Lenovo / Total Sales: $800

The purpose of this example is not simply to verify brand sales rankings. In practical business analytics, we must address the following strategic questions:

"Do the top few brands account for the vast majority of overall revenue?"

"Is revenue heavily concentrated in specific brands, or is it evenly distributed across multiple brands?"

To answer these questions, calculating the Running Total (cumulative sum) is essential.

2. How Running SUM Operates in Traditional DAX (The Barrier of Filter Context)

To calculate cumulative sales (Running Total) in traditional DAX, one had to master and strictly control two core concepts: 'Filter Context' and 'Context Transition'. A typical DAX cumulative measure takes the following form:

1) The Traditional DAX Mindset: "Refilter the Entire Table for Every Single Row"

Running Total DAX =

VAR CurrentDate = MAX('Calendar'[Date])

RETURN    CALCULATE(

        [Total Sales],

        FILTER(

            ALLSELECTED('Calendar'[Date]),

            'Calendar'[Date] <= CurrentDate

        )

    )

This approach executes visually highly inefficient operations:

• It captures the current row's date (CurrentDate) as a variable.
• It uses CALCULATE and FILTER to open the entire calendar table (ALLSELECTED) within the data model.
• It filters the table in its entirety under the condition "all dates less than or equal to the current date," and then re-aggregates sales for that specific region.

In other words, to calculate the cumulative value for December, it re-reads data from January through December; to calculate November, it re-reads from January through November—resulting in redundant, repetitive scanning. As data volume grows into tens of millions of rows, or when attempting to compute cumulative metrics based on string values (e.g., Brand Name, Customer Name) rather than dates, RANKX must be introduced, making the code extraordinarily complex.

3. Utilizing RUNNINGSUM Provided by Visual Calculation

Visual Calculation performs computations directly on top of the result table (Visual Matrix) already loaded into the visual object, rather than at the data model (engine) layer. By leveraging the new RUNNINGSUM function, complex filter context controls disappear entirely.

1) The Visual Calculation Approach (Entered directly in the Visual Calculation edit bar)

This code operates successfully because Visual Calculation functions like a "pre-sorted Excel sheet currently rendered on screen." Without the need to flip the model upside down to recalculate filter contexts, it performs a sequential operation (Scan)—adding the value of Row 2 to Row 1, and the value of Row 3 to that result, directly as loaded in the visual object.

2) Sorting Principles and Pitfalls of Visual Calculation ("The Sorting Trap")

To seamlessly extend this convenient RUNNINGSUM to Pareto Analysis (ABC Classification), there is a critical mechanism you must understand. Even if we have sorted the matrix on screen in descending order based on sales (Total Sales), simply entering the basic formula above can cause the resulting values to become completely scrambled.

• Root Cause: The moment the Visual Calculation window opens, the calculation engine internally resorts the data based on the primary unique field—the alphabetical order of the Brand field (Apple → Huawei → Lenovo → LG...)—and then computes the cumulative sum from top to bottom.
• The Problem: Consequently, the sales volume sequence breaks down, placing a lower-tier brand like Huawei ($1,200) in the second row of the cumulative calculation. This creates a cascade of errors that ultimately invalidates both the calculated market share percentages and the final ABC classifications.

3) Solution: Forcing Sort Direction via ORDERBY

The solution is remarkably simple. We explicitly instruct the calculation engine—which defaults to sorting by the first column (alphabetically)—to "unconditionally sort in descending order based on sales volume before accumulating." Adding the ORDERBY option inside the function cleanly resolves this issue.

By enforcing the sort direction directly within the expression, the visible sales ranking on screen perfectly aligns with the internal cumulative sum calculation direction, constructing an accurate foundation for Pareto analysis.

4. Practical Business Application: Cumulative Brand Sales and ABC Analysis

Let us implement "ABC Classification via Cumulative Sales Share (the 80/20 Rule)," one of the most frequently utilized frameworks in business practice. While traditional DAX required dozens of lines of complex code to derive rankings and cumulative ratios, Visual Calculation accomplishes this with just 3 intuitive fields.

Visual Configuration (Table Matrix)

• Rows: Product[Brand] (Sorted in descending order by sales)
• Values: [Total Sales]

Visual Calculation Step-by-Step

1) Calculate Cumulative Sales (Summing sequentially from top to bottom)

Calculates the running total across the sorted brand rows.

2) Calculate Cumulative Sales Share Relative to Total Brand Revenue

COLLAPSEALL is a dedicated Visual Calculation function that ignores all filters within the visual object to retrieve the overall Grand Total.

3) ABC Classification (80% or below = A, 95% or below = B, Remaining = C)

Categorizes brands into strategic performance tiers based on their cumulative share threshold.

5. Result Interface Example

Key Takeaway (Realizing Pareto's Principle)

Out of the 7 total brands, the top 2 brands (Apple and Samsung—representing approximately 28% of total brand count) account for 76% of overall revenue, classifying them into Tier A. In practical execution, this provides immediate clarity within seconds: business resources for inventory management and marketing focus must be concentrated on these two core brands.

Wrapping up

"From Technical Complexity to Analytical Substance"

In traditional Power BI, brand accumulation and ABC classification were perpetual pain points due to complex DAX logic and filter context management. Calculations frequently broke whenever a slicer or sort order was modified, wasting substantial development time.

However, the new Visual Calculation paradigm transforms business analytics by allowing users to manipulate data 'exactly as rendered on screen.' Freed from the swamp of technical complexity, analysts can now focus entirely on deriving actionable business insights. Open your Visual Calculation editor today and apply RUNNINGSUM and COLLAPSEALL. Your report development speed and execution performance will improve dramatically!

---

<Other posts on the blog>

• • A Snapshot That Halts the Filter Flow: The Decisive Moment a DAX VAR Becomes a 'Constant'
• • Why Your DAX Keeps Failing – An Anatomy of the CALCULATE Internal Execution Timeline
• • Advanced DAX - Pin "Other" to the Bottom : The Subtle Detail That Determines Readability, Custom Sort Order
• • Accumulation Part 3 : Periodic Comparison