cancel
Showing results for
Did you mean:
Super User

## Running total Issue

I am trying to create a running total using WINDOW function and I am not sure if I am overcomplicating but this is the only I can get it to work.

``````//my base data is this

| Year | Val |
|------|-----|
| 2020 | 100 |
| 2021 | 200 |
| 2022 | 300 |
| 2023 | 400 |

//I need a calculated table with running total
| Year | Val | rnt  |
|------|-----|------|
| 2020 | 100 | 100  |
| 2021 | 200 | 300  |
| 2022 | 300 | 600  |
| 2023 | 400 | 1000 |``````

I am writing a table expression like this which is doing the job. The rank is calculated to determine the correct TO parameter of WINDOW. Is there an easier way?

``````Table =
tbl,
"rnt",
VAR toVal =
RANKX (
WINDOW ( 1, ABS, -1, ABS, SUMMARIZE ( tbl, tbl[Year] ), ORDERBY ( tbl[Year] ) ),
tbl[Year],
,
DESC
)
VAR rnt =
CALCULATE (
SUM ( tbl[Val] ),
ALL ( tbl ),
WINDOW ( 1, ABS, toVal * -1, ABS, , ORDERBY ( tbl[Year], ASC ) )
)
RETURN
rnt
)``````

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

1 ACCEPTED SOLUTION
Power BI Team

Here is a simpler solution

Table2 = ADDCOLUMNS(tbl, "rnt", CALCULATE(SUM('tbl'[Val]), WINDOW(1, ABS, 0, REL, ALL(tbl[Year])), ALL(tbl)))

The key is to use ALL(tbl) to remove the filter on the [Val] column that's converted from row context to filter context by CALCULATE.
Power BI Team

Here is a simpler solution

Table2 = ADDCOLUMNS(tbl, "rnt", CALCULATE(SUM('tbl'[Val]), WINDOW(1, ABS, 0, REL, ALL(tbl[Year])), ALL(tbl)))

The key is to use ALL(tbl) to remove the filter on the [Val] column that's converted from row context to filter context by CALCULATE.

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors