Forum Discussion
Calculate running total based on Sort Column
I have a table where I am trying to calculate a running total based on a sort column with an additional filter as follows. This would have typically been a standard YTD formula but the start of the f...
Hi Anonymous
Use and modify the below Formula as per your need.
Running Total COLUMN = CALCULATE ( SUM ( 'table'[col1] ), ALL( 'table'), //ALLEXCEPT ( 'table', 'table'[] ) //If you want to group by any column 'table'[col2] <= EARLIER ( 'table'[col2] ) )https://www.wallstreetmojo.com/power-bi-running-total/
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit
Here's the table I'm using 'User_Payments' -
| User_ID | Tran_Date | Tran_Amt | Running_Deposits |
| 100 | 7/1/2021 | 100 | 300 |
| 100 | 7/15/2021 | 125 | 300 |
| 100 | 7/18/2021 | 75 | 300 |
| 105 | 6/1/2021 | 120 | 220 |
| 105 | 6/30/2021 | 100 | 220 |
| 110 | 7/15/2021 | 50 | 50 |
| 115 | 6/15/2021 | 115 | 200 |
| 115 | 7/1/2021 | 85 | 299 |
Running_Deposits is a calculated column using this DAX code :
Running_Deposits =
CALCULATE (
SUM ( 'User_Payments'[Tran_Amt] ),
ALLEXCEPT (
'User_Payments',
'User_Payments'[User_ID]
),
'User_Payments'[User_ID]
= EARLIER ( 'User_Payments'[User_ID] )
)
I'm getting the total for each User_ID, but not the running total. I'd like results like this:
| User_ID | Tran_Date | Tran_Amt | Running_Deposits |
| 100 | 7/1/2021 | 100 | 100 |
| 100 | 7/15/2021 | 125 | 225 |
| 100 | 7/18/2021 | 75 | 300 |
| 105 | 6/1/2021 | 120 | 120 |
| 105 | 6/30/2021 | 100 | 220 |
| 110 | 7/15/2021 | 50 | 50 |
| 115 | 6/15/2021 | 115 | 115 |
| 115 | 7/1/2021 | 85 | 200 |
I've tried <= and >= operators in the filters, but that does not yeild better results.
Thanks!
Ashish,
Thank you very much for sharing this. I'll work with it to get it into a calculated column rather than a measure as this is really just one component of a much larger need.
You are welcome.
I am so so thankful to you, I was looking for this solution for hours.
You are welcome.
