Forum Discussion

Ncf5031's avatar
Ncf5031
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Resetting Running Total Based On It's Own Value

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 i...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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's avatar
Ncf5031
Icon for Advocate I rankAdvocate I
4 years ago

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:
 
DateTotal ProductProductRTGrouping 100MJob Location
9/25/2020 96246096246016
9/26/2020 3383240434570016
9/27/2020 3371960771766016
9/28/2020 33799001109756016
9/29/2020 38688301496639016
9/30/2020 38592501882564016
10/1/2020 38644602269010016
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
    Ncf5031
    Icon for Advocate I rankAdvocate I
    4 years ago

    Thank you kindly for your help.