Forum Discussion

patbyr's avatar
patbyr
Frequent Visitor
3 years ago

Running total by group and reset it when another column has value that trigger for it to reset

Hello,   I'm not sure if anyone will respond to my reply that I did in someone else's forums (Running Total Reset after specific column change) but here is my post below which is same in the link. ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi patbyr ,

 

Please try like:

Column = 
VAR _index_1 =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        'Table'[Index] <= EARLIER ( 'Table'[Index] )
            && 'Table'[Reset?] = "Yes",
        ALLEXCEPT ( 'Table', 'Table'[Index] )
    ) + 0
VAR _index_2 =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        'Table'[Index] >= EARLIER ( 'Table'[Index] )
            && 'Table'[Reset?] = "Yes",
        ALLEXCEPT ( 'Table', 'Table'[Index] )
    )
VAR _index_3 =
    IF ( ISBLANK ( _index_2 ), MAX ( 'Table'[Index] ) + 1, _index_2 )
VAR _result =
    IF (
        'Table'[Reset?] <> "Yes",
        CALCULATE (
            SUM ( 'Table'[Total Strokes] ),
            FILTER(
                ALL('Table'),
            'Table'[Index] <= EARLIER ( 'Table'[Index] )
                && 'Table'[Index] > _index_1
                && 'Table'[Index] < _index_3
            )
        ),
        0
    )
RETURN
    _result

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

patbyr's avatar
patbyr
Frequent Visitor
3 years ago

Gao,

 

Thank you so much for getting back with me on this! That picture above is exactly what I wanted it to do. I went ahead and put your forumla into my Power Bi and this is what I got. (see picture below) I think we need to add something in the formula that it will know which TD-XXXX because that is the group I'm working off of for the total strokes along with looking into the reset? column to make it reset. Please see couple of pictures. Side note: the red box below is the small picture snap shot of what I provided above as an example but wanted to make sure you are aware of the whole thing.