Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AMPAllie
Helper II
Helper II

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

AMPAllie_0-1657133530823.png

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
v-jayw-msft
Community Support
Community Support

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

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

View solution in original post

4 REPLIES 4
v-jayw-msft
Community Support
Community Support

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

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

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

That returned this:

AMPAllie_0-1657135872302.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.