[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 1: Basic Running Total
In our last post, we theoretically explored how the ALL function releases time filters to set the stage for accumulation analysis. Today, we move into the practical application: implementing a Running Total using real-world IT device sales data and interpreting it through the lens of business strategy.
If your professional dashboard only shows monthly sales charts, it’s like checking only the "instantaneous speed" of a car. A Running Total, however, is the "odometer" that shows how far you have traveled toward your destination. Especially in organizations with annual quotas, accumulation data is more than just a performance report—it is the most powerful basis for deciding whether to commit additional resources in the second half of the year.
1. Practical Example Data Structure
We will design basic measures based on the provided July to December data.
- Base Measure: Total Sales = SUM(Sales[Sales])
- Total Volume: $3,259,000 (Total for the second half)
2. Why Do We Need a Running Total?
First, let's look at the monthly sales:
This table simply shows monthly results. However, this information alone makes it difficult to answer questions like:
- How much revenue has accumulated so far?
- What is the pace of progress relative to our goals?
- Are we moving faster than at the same point last year?
This is exactly why we need a Running Total.
3. Writing the Running Total DAX
The most fundamental pattern for calculating accumulation in Power BI is as follows:
Running Total =
CALCULATE(
[Total Sales],
FILTER(
ALL('Sales'), -- Restore the full date table
' Sales '[Date] <= MAX(' Sales '[Date]) -- Accumulate values up to the current date
)
)
The step-by-step operation of this formula is as follows:
- ALL('Sales'): This restores the entire date dataset that was hidden behind the filters.
- FILTER Condition: It filters the restored data to retain only the dates prior to or equal to the current point (MAX Date).
- CALCULATE: It re-calculates the sales for that specific period and stacks them up.
In short, you can understand a Running Total as a calculation structure that gathers and sums all data from the past up to the present.
Field Know-how: Why is this pattern so powerful?
Many users rely on the TOTALYTD function first. However, in practice, the CALCULATE + FILTER + ALL pattern is used more frequently for the following reasons:
When the fiscal year starts in a month other than January (e.g., March).
- When you need to accumulate only during a specific promotion period.
- When calculating cumulative sales specifically since a product launch.
- While TOTALYTD has rigid constraints, this pattern can adapt immediately just by changing the conditions.
4. Marketing Insights from the Data (Numerical Analysis)
By placing the Running Total (Direction) and Month-over-Month (MoM) growth (Velocity) side-by-side, you can evaluate the quality of growth.
- Running Total → The overall direction of growth.
- MoM → The flow of monthly changes that created that growth.
By checking these together, you can identify growth "Peaks" (like in September) or "Jumps" (like in November). A waterfall chart can show these changes step-by-step, allowing you to see at a glance exactly how the accumulation was formed.
[Link] DAX Deep Dive 05: MoM Growth - How to Read the 'Pulse' of Your Business Beyond Just Numbers (Coming Soon)
5. Power BI Visualization Strategy: Catching Management's Eye
In dashboards for management reports, the Combo Chart (Line and Stacked Column Chart) is most widely used.
- Columns: Monthly Sales → This month’s performance.
- Line: Running Total → Progress rate toward the annual goal.
Adding a Goal line behind the accumulation line increases the persuasiveness of your report. If the goal is $4,000,000, seeing a $2,110,000 accumulation in October immediately communicates that you are at roughly 53% of the target. This provides a very intuitive message to executives.
Practical Visualization Tip: The Ribbon Combo Chart
While a basic combo chart shows sales increases, it’s hard to identify "who created the growth." In practice, I often use a Ribbon + Line Combo Chart. This visual shows product-specific sales flows, total sales, and MoM changes simultaneously. It allows you to identify the role each SKU plays in the total revenue and which products are driving upward trends. By observing the "Peak" or "Jump" periods, you can quickly see which SKU led the growth at that specific moment.
[Related Post] Master the Ribbon Combo Chart (Coming soon)
Wrapping up
The moment you add a Running Total, your report transforms from a "record of the past" into a "map for the future." This is because the momentum of growth, which was invisible when looking only at monthly sales, begins to reveal itself.
Core Summary:
The ALL function is the key to summoning "past data" hidden behind filters.The point where the slope of the accumulation curve changes is the optimal time to deploy marketing resources.
What's Next: Part 2 — Selective Accumulation
In our next post, we will look at even sharper practical techniques, such as accumulating data specifically for "the first 30 days after a product launch" or "only during a campaign period." If you have any questions, please leave them in the comments!
[Series Index]
- Part 1 - Basic Running Total (Current Post)
- Part 2 - Selective Accumulation (Coming Soon)
<Other posts on the blog>
Comments
Post a Comment