running total

[Anonymous]

I am having issues creating a running total on the field "Count Charged off loans" with the output the way it is in the green column. (the running total resets when the tier field changes). Please i need help

[parry2k · Accepted solution]

use this measure 

RT = CALCULATE( SUM( Table1[Count Charged off Loans] ), 
FILTER( ALLEXCEPT( Table1, Table1[Tier] ), Table1[Loan Life] <= MAX( Table1[Loan Life] ) ))

[Anonymous]

Tier	Count Booked Loans	Loan Life	Count Charged off Loans	Expected Result
6	23	0	0	0
6	23	9	1	1
6	23	17	2	3
6	23	19	1	4
6	23	20	1	5
6	23	21	1	6
6	23	22	4	10
6	23	25	2	12
8	23	0	0	0
8	23	2	1	1
8	23	16	3	4
8	23	22	6	10

I actually want it as a calculated measure

[v-yuezhe-msft · Accepted solution]

@Anonymous,

You can also use the following DAX to calculate the measure.

Expected result = 
    CALCULATE (
        SUM ( Table1[Count Charged off Loans] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Loan Life] <=MAX(Table1[Loan Life])
        ),VALUES(Table1[Tier])
    )

Regards,
Lydia

[AkhilAshok]

You can create a calculated column like this:

Count Charged off loans RT =
VAR CurrentTier = Table[Tier]
VAR CurrentLoanLife = Table[Loan Life]
RETURN
    CALCULATE (
        SUM ( Table[Count Charged off Loans] ),
        FILTER (
            ALL ( Table ),
            Table[Tier] = CurrentTier
                && Table[Loan Life] <= CurrentLoanLife
        )
    )

[Anonymous]

I would like it as a measure and not a calculated column cos the end game is to use it on a graph

[parry2k · Accepted solution]

use this measure 

RT = CALCULATE( SUM( Table1[Count Charged off Loans] ), 
FILTER( ALLEXCEPT( Table1, Table1[Tier] ), Table1[Loan Life] <= MAX( Table1[Loan Life] ) ))

[Anonymous]

Thanks for the response. This works, but the moment i drop a slicer on the report it works in a weird manner. Any suggestions please?

[v-yuezhe-msft]

@Anonymous，

What field do you use to create a slicer? What result would you like to get by using slicer?

Regards,
Lydia

[Anonymous]

I fixed it already, Thank you so much 

[LivioLanzo]

Hi @Anonymous

could you post a dataset which can be copied/pasted? Also do you want to do this in a measure or calculated column?