Implementing Running SUM in Power BI Visual Calculation (Part 2) WINDOW Function

In the previous post, we explored how to calculate cumulative sales and ABC classification by brand using the simplest and most intuitive RUNNINGSUM function. However, in business practice, beyond simple end-to-end accumulation, various calculation requirements arise depending on the analytical objective.

"What if you only want to calculate the total sales for the top 3 brands?"

"What if you want to group and calculate only the previous step and the current step together?"

Moments like these inevitably arrive when you need to freely control the calculation boundary (sliding window). The master key in Visual Calculations for these scenarios is the WINDOW function. In Part 2, based on the same brand sales data, we will examine the powerful control of the WINDOW function and how it carves out visual space to execute calculations.

[Link]

Basic Example Data

Sample table

1. Understanding the Structure of the WINDOW Function

While RUNNINGSUM is an ultra-convenient template built exclusively for cumulative calculations, the WINDOW function is a precision tool that allows users to specify the starting and ending points of a calculation boundary (window) within a visual object as desired.

WINDOW (

    from, [from_type],

    to, [to_type],

    [relation], [orderBy], [blanks], [partitionBy]

)

Rather than directly returning a scalar value, this function returns a 'table (visual space)' corresponding to the scope specified by the user.

2. Core Parameters: ABS, REL, and ORDERBY

To master the WINDOW function, you must understand three foundational concepts that determine the spatial coordinate system:

  • ABS (Absolute): Finds a fixed position based on the beginning or end of the visual object.
    • 1, ABS unconditionally signifies the "first row (topmost)" regardless of the underlying data.
  • REL (Relative): Denotes a relative distance based on the current row where the calculation is being evaluated.
    • 0, REL signifies "the current row where I am standing," while -1, REL signifies the "immediately preceding row."
  • ORDERBY: Explicitly designates the sort order that serves as the baseline for accumulation. To avoid "The Sorting Trap" discussed in Part 1, you must explicitly specify descending order by sales volume.

3. Combining SUMX with the WINDOW Function

Because the WINDOW function returns a space (table), to derive the actual sum, it must be paired with an iterator function like SUMX, which reads through this space row by row and aggregates the values.


Window RunningSales = SUMX( WINDOW( 1, ABS, 0, REL, ORDERBY([Total Sales], DESC) ), [Total Sales] )


Window Running Sales Calculation Process


Window Running Sales Calculation Process


By fixing the starting point at 1, ABS (fixed) and setting the ending point to 0, REL (current row), the WINDOW expands as it moves down each row, cumulatively summing revenue from the first row down to the current row.

Wrapping Up

In Part 2, we covered the fundamental concepts of the WINDOW function along with the ABS and REL coordinate systems, and explored how to compute a cumulative total (Running Total) by locking the starting position.

What happens, then, if instead of locking the first row (1, ABS), we change the starting point to a relative position (-2, REL) that moves dynamically alongside the current row?

In the upcoming Part 3, we will cover how to utilize the WINDOW function to implement Moving Sums and 3-row Moving Averages—some of the most frequently used analytical techniques in real-world business practice.

Go to Next Post

<Other posts on the blog>

Comments

Post a Comment

Popular posts from this blog

DAX Deep Dive 02 : Calculated Column vs. Measure – The Essential Difference Every Power BI Pro Must Know

Image
The first step to mastering DAX is understanding the "identity of a value." You must clearly define whether what you are creating is "fixed historical data" or a "real-time response" that changes based on a user's question. 1. Calculated Column: A Fixed State Determined at Data Loading A Calculated Column is computed row by row during the data refresh process and is physically stored as a value within the data model. Once calculated, it does not change based on slicers or filters in the report. Calculation Timing: Performed only once during the data refresh. Operating Principle (Row Context): It scans each row one by one and completes the calculation using only the values present in that specific row. System Characteristics: Slicers or filters on a report cannot change the "result value" of a Calculated Column. Slicers merely determine "which rows to show" (Visibility). Primary Uses: Values to be used as slicer items (e.g., Age Gr...
Read more

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more