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

Regular Visitor

## Running Total Quick Measure not calculating correctly

I have a table of projects listed with kW values and a start year as well as other characteristics. There can be multiple projects per year (or none) depending on what is selected on the slicer. I am attempting to show the cost total per year and the cumulative (running total) kW with each successive year. However the running total quick measure is not working correctly.

Instead of showing a cumulative total which grows with each year, it is showing the kW total for each year multiplied by the position order of each year sequentially. So the first year with a kW value in the table appears correctly but the second year shows the total for that year (ignoring the first year) multiplied by 2, the third year shows the third year total multiplied by 3, etc. Years with no projects are skipped over. What am I missing here?

Below is the DAX input and the data I am getting back with the sum of kWs also shown (correctly) for each year.

The DAX input:

Electrification kW running total in Start Year =
CALCULATE(
SUM('Data Table'[Electrification kW]),
FILTER(
ALLSELECTED('Data Table'[Start Year]),
ISONORAFTER('Data Table'[Start Year], MAX('Data Table'[Start Year]), DESC)
)
)

I used the same DAX input with a simple set of data that I randomly created and I get the correctly calculated running total so I know the measure is written approriately.

2 ACCEPTED SOLUTIONS
Community Support

You can try to use the following measure formula if it suitable for your requirement:

``````formula =
VAR _start =
MINX ( ALLSELECTED ( 'Data Table' ), [Start Year] )
VAR currYear =
MAX ( 'Data Table'[Start Year] )
VAR summary =
SUMMARIZE (
FILTER (
ALLSELECTED ( 'Data Table' ),
[Building] IN VALUES ( 'Data Table'[Building] )
),
[Start Year],
[Building],
"Total",
VAR currValue =
SUM ( 'Data Table'[Electrification kW] )
RETURN
IF ( currValue <> 0, DIVIDE ( currValue, [Start Year] - _start + 1 ) )
)
RETURN
SUMX ( FILTER ( summary, [Start Year] <= currYear ), [Total] )``````

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor
I'm not sure why but removing "[Start Year]" from the ALLSELECTED prompt resolved my issue. This also works without using ISONORAFTER as shown in the second option below. @v-shex-msft 's solution may work as well but I haven't given it a shot yet.

Working Input #1:
Electrification kW running total in Start Year =
CALCULATE(
SUM('Data Table'[Electrification kW]),
FILTER(
ALLSELECTED('Data Table'),
ISONORAFTER('Data Table'[Start Year]MAX('Data Table'[Start Year]), DESC)
)
)

Working Input #2:
Electrification kW running total in Start Year =
CALCULATE(
SUM('Data Table'[Electrification kW]),
FILTER(
ALLSELECTED('Data Table'),
'Data Table'[Start Year] <= MAX('Data Table'[Start Year])
)
)
4 REPLIES 4
Regular Visitor
I'm not sure why but removing "[Start Year]" from the ALLSELECTED prompt resolved my issue. This also works without using ISONORAFTER as shown in the second option below. @v-shex-msft 's solution may work as well but I haven't given it a shot yet.

Working Input #1:
Electrification kW running total in Start Year =
CALCULATE(
SUM('Data Table'[Electrification kW]),
FILTER(
ALLSELECTED('Data Table'),
ISONORAFTER('Data Table'[Start Year]MAX('Data Table'[Start Year]), DESC)
)
)

Working Input #2:
Electrification kW running total in Start Year =
CALCULATE(
SUM('Data Table'[Electrification kW]),
FILTER(
ALLSELECTED('Data Table'),
'Data Table'[Start Year] <= MAX('Data Table'[Start Year])
)
)
Regular Visitor

Hi @v-shex-msft, thanks for the suggestion! I can't share the full pbix but below is a table of the input data that is fed into the chart.

 Building Electrification kW Add Capital Cost Start Year A 0 \$1,902,734 2026 B 0 \$1,735,180 2025 C 142 \$249,500 2025 D 482 \$756,000 2027 D 120 \$22,000 2027 E 174 \$1 2029 E 261 \$488,350 2029 F 984 \$1 2032 F 380 \$834,150 2032 G 376 \$1 2031 H 0 \$1,806,621 2034 J 336 \$631,250 2026 J 617 \$19,295,000 2026 K 0 \$1,267,386 2026
Community Support

You can try to use the following measure formula if it suitable for your requirement:

``````formula =
VAR _start =
MINX ( ALLSELECTED ( 'Data Table' ), [Start Year] )
VAR currYear =
MAX ( 'Data Table'[Start Year] )
VAR summary =
SUMMARIZE (
FILTER (
ALLSELECTED ( 'Data Table' ),
[Building] IN VALUES ( 'Data Table'[Building] )
),
[Start Year],
[Building],
"Total",
VAR currValue =
SUM ( 'Data Table'[Electrification kW] )
RETURN
IF ( currValue <> 0, DIVIDE ( currValue, [Start Year] - _start + 1 ) )
)
RETURN
SUMX ( FILTER ( summary, [Start Year] <= currYear ), [Total] )``````

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.