cancel
Showing results for
Did you mean:
Helper I

## Running Total for Measure by Category

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.
1 ACCEPTED SOLUTION
Community Support

Hi @AMPAllie ,

``````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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
4 REPLIES 4
Community Support

Hi @AMPAllie ,

``````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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

``````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] )``````
Helper I

That returned this:

Helper I

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.