Forum Discussion

PHanus3's avatar
PHanus3
New Member
4 years ago
Solved

Running Total for values calculated by measure

I would like to ask you how to solve running total for values calculated by measure (Searching already couple of days for solution and can not find anything).  Can you please help me someone? Thanks a lot in advance!

 

The issue is:

I have model with only one table with one column "year of project" which contains numbers from 0 to 30. everything else are just measures and parameters.

 

I let user to set:
Parameter Inflation,
Parameter Value A,
Parameter Value B.

 

I calculate measures:
value A adjusted by inflation = [Value A] * POWER((1+[Inflation]),MAX([year of project])
value B adjusted by inflation = [Value B] * POWER((1+[Inflation]),MAX([year of project])
Total = [value A] + [value B]

then I want to calculate running total, but I am stucked and dont know how. 

 

Matrix I want to see at the end:

year of projectvalue avalue btotal???running total???

1

2466
236915
3481227

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    hI PHanus3 

    To sum the cumulative values of [Total] for each year, you could use a measure like this (change table/column names as needed):

    Running Total = 
CALCULATE (
    SUMX (
        VALUES ( YearOfProject[Year of Project] ),
        [Total]
    ),
    YearOfProject[Year of Project]
        <= MAX ( YearOfProject[Year of Project] )
)

    In your particular example, since the underlying measures form a geometric sequence, you could also consider using the formula for the sum of a geometric sequence which would avoid iteration.

     

    Regards,

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

    hI PHanus3 

    To sum the cumulative values of [Total] for each year, you could use a measure like this (change table/column names as needed):

    Running Total = 
CALCULATE (
    SUMX (
        VALUES ( YearOfProject[Year of Project] ),
        [Total]
    ),
    YearOfProject[Year of Project]
        <= MAX ( YearOfProject[Year of Project] )
)

    In your particular example, since the underlying measures form a geometric sequence, you could also consider using the formula for the sum of a geometric sequence which would avoid iteration.

     

    Regards,

    Owen

  • PHanus3's avatar
    PHanus3
    New Member
    4 years ago

    Thank you a lot Owen!

    It works exactly as expected.

     

    Pavel