Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous,

    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's avatar
    Anonymous
    2 years ago
    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])
        )
    )
Anonymous's avatar
Anonymous
Not applicable
2 years ago
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])
    )
)