Running Total Quick Measure not calculating correctly

[treader103]

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. 

[Anonymous · Accepted solution]

Hi @treader103,

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

[treader103 · Accepted solution]

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. @Anonymous '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])

    )

)

[treader103]

Hi @Anonymous, 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

[Anonymous · Accepted solution]

Hi @treader103,

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

[Anonymous]

Hi @treader103 ,

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.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng