running total

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

Need Help

Show and Tell

Tips and Tricks

parry2k

7 years ago

use this measure

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

Anonymous

7 years ago

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

9 Replies

LivioLanzo
Solution Sage
7 years ago
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?

AkhilAshok

Solution Sage

7 years ago

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
Not applicable
7 years ago
I would like it as a measure and not a calculated column cos the end game is to use it on a graph
- parry2k
  Super User
  7 years ago
  use this measure
  
  RT = CALCULATE( SUM( Table1[Count Charged off Loans] ),
  FILTER( ALLEXCEPT( Table1, Table1[Tier] ), Table1[Loan Life] <= MAX( Table1[Loan Life] ) ))

Anonymous
Not applicable
7 years ago
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
- Anonymous
  Not applicable
  7 years ago
  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

Forum Discussion

running total

9 Replies

Related content

Running Totals

Running total

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue

SumX? - cumulative running total

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