cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors