cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors