Forum Discussion
Running total with Reset
Hi there,
I am trying to create a column that has a running total, but resets if a column has a 1 (if the column has a value of 0, then the score does not reset). I followed this previous post, but I am getting something odd.
https://community.powerbi.com/t5/Desktop/Running-Total-Reset-based-on-Column-Attribute/m-p/251130
The actual line items with dates show the correct running total (with reset). however, the dates that do not have activities show a value of 1090 (which happens to be the running total on 7/29/19).
For example, based on the data table below, Dates of 12/31/18 to 1/22/19 show a value of 1090, which causes my line chart to be incorrect.
The sample data set is below. What happens is that I choose a single customer id, and have a list of all the activities that the customer had on our website. Each activity is assigned a specific score and these scores are aggregated until they need to be reset, which is denoted by the "Reset score" column (1=reset, 2=no reset). The "Running score with reset" is what I'm trying to get, but I'm getting scores of 1090 for all the dates that are not listed in the table. .
Here are the calculations I am using:
Data set:
| Id | ID Index | Activity Date | Activity Score | Running Score | reset score | Max Date | Running score with reset |
| N0EAJ | 1 | 12/30/2018 | 50 | 70 | 1 | 12/30/2018 | 70 |
| N0EAJ | 2 | 12/30/2018 | 20 | 70 | 0 | 12/30/2018 | 70 |
| N0EAJ | 3 | 1/23/2019 | 50 | 120 | 1 | 1/23/2019 | 50 |
| N0EAJ | 4 | 1/24/2019 | 50 | 220 | 0 | 1/23/2019 | 100 |
| N0EAJ | 5 | 1/24/2019 | 50 | 220 | 0 | 1/23/2019 | 150 |
| N0EAJ | 6 | 2/12/2019 | 50 | 270 | 1 | 2/12/2019 | 50 |
| N0EAJ | 7 | 2/17/2019 | 50 | 390 | 1 | 2/17/2019 | 120 |
| N0EAJ | 8 | 2/17/2019 | 50 | 390 | 0 | 2/17/2019 | 120 |
| N0EAJ | 9 | 2/17/2019 | 20 | 390 | 0 | 2/17/2019 | 120 |
| N0EAJ | 10 | 2/26/2019 | 50 | 490 | 1 | 2/26/2019 | 100 |
| N0EAJ | 11 | 2/26/2019 | 50 | 490 | 0 | 2/26/2019 | 100 |
| N0EAJ | 12 | 3/5/2019 | 50 | 540 | 1 | 3/5/2019 | 50 |
| N0EAJ | 13 | 3/11/2019 | 50 | 590 | 1 | 3/11/2019 | 50 |
| N0EAJ | 14 | 3/19/2019 | 50 | 640 | 1 | 3/19/2019 | 50 |
| N0EAJ | 15 | 3/21/2019 | 50 | 690 | 1 | 3/21/2019 | 50 |
| N0EAJ | 16 | 3/22/2019 | 50 | 740 | 0 | 3/21/2019 | 100 |
| N0EAJ | 17 | 3/24/2019 | 50 | 790 | 0 | 3/21/2019 | 150 |
| N0EAJ | 18 | 3/28/2019 | 50 | 840 | 1 | 3/28/2019 | 50 |
| N0EAJ | 19 | 4/24/2019 | 0 | 840 | 0 | 3/28/2019 | 50 |
| N0EAJ | 20 | 5/8/2019 | 50 | 890 | 1 | 5/8/2019 | 50 |
| N0EAJ | 21 | 6/4/2019 | 50 | 940 | 1 | 6/4/2019 | 50 |
| N0EAJ | 22 | 6/7/2019 | 50 | 990 | 0 | 6/4/2019 | 100 |
| N0EAJ | 23 | 7/22/2019 | 50 | 1040 | 1 | 7/22/2019 | 50 |
| N0EAJ | 24 | 7/29/2019 | 50 | 1090 | 0 | 7/22/2019 | 100 |
This is a sample of the resulting table I'm getting with my current calculations.
Any assistance would be great!
Thank you.
Thanks for your input. I was able to figure out.
Basically what was happening it was calling the customer ID from a different table instead of within the same table. Once I did this minor change, it worked.
(Like you, I tried it with the simple table I sent in my original post, and it worked with the same calculations and measures).
Thanks!
3 Replies
hi, Anonymous
I have test on my side, it works well on my side.
Could you please share your sample pbix file for us have a test? there may be something wrong in other.
Best Regards,
Lin
Thanks for your input. I was able to figure out.
Basically what was happening it was calling the customer ID from a different table instead of within the same table. Once I did this minor change, it worked.
(Like you, I tried it with the simple table I sent in my original post, and it worked with the same calculations and measures).
Thanks!
