Running Total for Measure by Category

[AMPAllie]

Hello All. This seems like it should be very simple, but I can't figure it out.  I have a Matrix like so

First three columns are in one table, Labor Spent in another and Delta is a measure:

Delta = IF(MAX('PR PH TS Names and Budget'[PH#]) <> Blank(),

SUM('PR PH TS Names and Budget'[Invoice Amount/Budget])-SUM('Labor Spent'[Labor Spent]),

Blank())

What I need is a column for running total of Delta for each row that has a PH# and starts over at the next PR#

Any help is much appreciated.

[Anonymous · Accepted solution]

Hi @AMPAllie ,

Please refer this formula:

RunningTotal =
SUMX (
    ALLEXCEPT (
        'PR PH TS Names and Budget',
        'PR PH TS Names and Budget'[PR#],
        'PR PH TS Names and Budget'[PH#]
    ),
    [Delta]
)

Or

RunningTotal =
SUMX (
    FILTER (
        ALLSELECTED ( 'PR PH TS Names and Budget' ),
        'PR PH TS Names and Budget'[PR#]
            = SELECTEDVALUE ( 'PR PH TS Names and Budget'[PR#] )
            && 'PR PH TS Names and Budget'[PH#]
                = SELECTEDVALUE ( 'PR PH TS Names and Budget'[PH#] )
    ),
    [Delta]
)

 If it still doesn't work, please share some sample data.

Best Regards,

Jay

[tamerj1]

Hi @AMPAllie 

please try

Running Total =
VAR CurrentPH =
    SELECTEDVALUE ( TableName[PH#] )
VAR T1 =
    CALCULATETABLE (
        VALUES ( TableName[PR#] ),
        ALLEXCEPT ( TableName, TableName[PH#] ),
        ALLSELECTED ( TableName )
    )
VAR T2 =
    FILTER ( T1, TableName[PH#] <= CurrentPH )
RETURN
    SUMX ( T2, [Delta] )

[AMPAllie]

That returned this:

[AMPAllie]

I found my typo...corrected it. But now I get an error. I should have mentioned...the PH# values are not unique. They repeat over and over.