Resetting Running Total Based On It's Own Value

[Ncf5031]

Hello all,

I'm needing some help with some seemingly simple DAX that is just stumping me. 

What I have now is a measure for the running total of a column that tracks pounds of product. Here is the DAX.

Product RT =

CALCULATE(

SUM(Product Table[TotalProduct]),

FILTER(

All(DateTable[REPORTING_DT]),DateTable[REPORTING_DT] <=MAX(DateTable[REPORTING_DT])))

The running total works fine, but what I'm needing is a way to reset the value once it reaches 100,000,000. I have seen many examples of doing something similar by referencing dates, but unfortunately I haven't been able to apply that technique to this situation. Any help is much appreciated!

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file, and I hope the below helps.

All measures are in the attached pbix file.

[Ncf5031]

Would there be a way to do this without an index column? I have entries into the table coming from mulitple job locations and I'm trying to do the product total per job location. I would have mentioned that before but I was unaware that the index would be necessary. 

The only way that I can get the running total to accumulate correctly is from the formula that I posted above. When using the formula in your example .pbix it would simply sum the entire column and insert that number into each row.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please share your sample pbix file, and then I can try to provide more accurate solution that suits your case.

Thanks.

[Ncf5031]

For some reason I am unable to attach an example .pbix, so I will try to explain my situation a bit better.

So, I have the listed the formula above as the way in which I have calculated a running total. This running total works when I apply a filter for job location. So when I have job location selected as "Location 2" the running total is correct for that location. 

Thanks to you, I have also been able to establish a group measure that counts how many times that total has gone past 100,000,000. What I am stuck on is that since I don't have an index column I am unable to use your DAX for the reset cumulative which is as follows:

Reset cumulative: =

VAR _currentgrouping = [Grouping every 100:]

RETURN

SUMX (

FILTER (

ALL ( Data ),

Data[Index] <= MAX ( Data[Index] )

&& [Grouping every 100:] = _currentgrouping

),

Data[Value]

).

My data looks as follows in table format:

Date	Total Product	ProductRT	Grouping 100M	Job Location
9/25/2020	962460	962460	1	6
9/26/2020	3383240	4345700	1	6
9/27/2020	3371960	7717660	1	6
9/28/2020	3379900	11097560	1	6
9/29/2020	3868830	14966390	1	6
9/30/2020	3859250	18825640	1	6
10/1/2020	3864460	22690100	1	6

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your reply.

I can see that you have a Date column. This can be used in the formula, instead of using Index column.

I tried to create another sample pbix file like the attached, and please check.

Thanks.

[Ncf5031]

Thank you kindly for your help.

[davehus]

Hi @Ncf5031 ,

There's a pattern here from @v-yingjl that might work for you.

https://community.powerbi.com/t5/Desktop/Cumulative-Running-Total-that-resets-after-other-column-hit...