Skip to main content
cancel
Showing results for 
Search instead 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

Reply
treader103
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.

treader103_0-1718146540683.png

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

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

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

View solution in original post

treader103
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])
    )
)

View solution in original post

4 REPLIES 4
treader103
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])
    )
)
treader103
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.

 

BuildingElectrification kW AddCapital CostStart Year
A0$1,902,7342026
B0$1,735,1802025
C142$249,5002025
D482$756,0002027
D120$22,0002027
E174$12029
E261$488,3502029
F984$12032
F380$834,1502032
G376$12031
H0$1,806,6212034
J336$631,2502026
J617$19,295,0002026
K0$1,267,3862026

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

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

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.