Running total as a column but by year and Category also

[DavidWaters100]

Hi,

I need to tweak the below column calculation which is a running total.

The calculation is working but I also need it to work seperately per year and per "Type" in my data set.  At the moment it is just a running total of each month combined (the [Period]), regardless of the Year and "Type".

I can share a file if it helps - any help greatly appreciated.  I have to try to do this using a column, not a measure. 

Thanks! 

Running Total =

CALCULATE (

    SUM ( Sheet1[Amount] ),

    ALL(Sheet1),

    Sheet1[Period] <= EARLIER (Sheet1[Period])

)

[mahoneypat · Accepted solution]

You can just adapt your measure with ALLEXCEPT() in place of ALL().  Just include the columns you don't want to remove the filter from.  For example - ALLEXCEPT(Sheet1, Sheet1[Year]) will keep the current Year context in place. You can add 1 or move columns there.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[DavidWaters100]

Hi @mahoneypat 

Thanks for your reply - this worked for the year, but I can't get the category to work also which is "Ref" per below.  Any ideas what I am doing wrong please?  I get an error saying "cannot convert value Ref1 of type text to type True/False"?

Running Total =

CALCULATE (

    SUM ( Sheet1[Amount] ),

    ALLEXCEPT(Sheet1,Sheet1[Year]),Sheet1[Ref],

    Sheet1[Period] <= EARLIER (Sheet1[Period])

)

[DavidWaters100]

Missed a bracket!  Thank you very much!

Running Total =

CALCULATE (

    SUM ( Sheet1[Amount] ),

    ALLEXCEPT(Sheet1,Sheet1[Year],Sheet1[Ref]),

    Sheet1[Period] <= EARLIER (Sheet1[Period])

)

[mahoneypat]

Both columns need to be listed in the ALLEXCEPT

ALLEXCEPT(Sheet1,Sheet1[Year],Sheet1[Ref]),

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat