Forum Discussion
Helper IRunning Total solution for slicer
Ok. I played with this some more. Please try this expression. Having account in both the table and the slicer makes this a challenge.
RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)Regards,
Pat
Microsoft EmployeePlease try this expression as a measure (replacing Table name "Total" with 'data').
RunningTotal =
VAR thisdate =
SELECTEDVALUE ( Total[Date] )
RETURN
CALCULATE (
SUM ( Total[Amount] ),
ALL ( Total ),
VALUES ( Total[Account] ),
Total[Date] <= thisdate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Helper IHey mahoneypat , Thanks!
This worked! Just want to ask if there is something else it can be done.
This only works fine if I filter a specific account, but when I want to show all accounts, it doesnt calculate the "global" running total, it is doing it by account.
This is what I see when I filter Month 2 and show all accounts. The running total starts fine on 35, but then on account 102 it starts again from zero. I would also like to see a sort of "global" running total ("Expected running total" down below)
| Account | Month | Date | Amount | Running Total | Expected Running Total |
| 101 | 2 | 2/15/2020 | 15 | 35 | 35 |
| 101 | 2 | 2/29/2020 | 15 | 50 | 50 |
| 102 | 2 | 2/15/2020 | 8 | 12 | 62 |
So, if I filter account 101 the running total should show 50 at the end (even if I filter Month 2 or if show all months), also, if I filter account 102, running total should be 12 (no matter what Month I filter or if there is no month filter) and If i show All accounts, it should calculate 62 at the end (even if I filter Month 2 or show all months).
If you got any idea for this, it would be great!
- mahoneypat
Yes. You can just take the VALUES( ) clause out of the CALCULATE(). You only had one account in the example data, so assumed (incorrectly) you would want it that way.
Regards,
Pat
- cpdanielmc21
Hi mahoneypat
That's what I thought, so I erased that line, but get the following result (no filters applied):
Running total is weird now, and If suppose, I apply filter month "2" and no filer on account, running total should be 62, but shows 47 on last line. If i happen to also add filter Account "102", the running total now should be 12, but nope, it shows 47.
😞
- mahoneypat
Try writing a new measure that references the original measure. This one will iterate over each account and add all of the running totals together. See if that works instead. Typing on a tablet. Sorry.
New = Sumx(values(table[account]), [original measure])
If not will look more later.
Pat