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

[patbyr]

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. May want to use the link to see the download attachment that this super user, Fowmy, did because I followed step by step he did. I hope someone can help me to make this possible.

I did so much research on "running total by group with reset in this column". It was amazing to see there are so many different ones out there but just couldn't find anything close to what I'm trying to accomplish.

Thank you so much for providing a download attachment for it. It helps alot on understanding how you set it up. I did follow step by step as what you provided above BUT would like to add a reset into it. The running total is adding up just like how you have it set up, but I would like for it to reset to 0 once it recognizes the "Yes" in the “Reset?” column and then start adding the running total from there. Here is a screen shot below. The top picture is what I currently have in my power bi query editor while bottom picture is what I would like for it do it. Please let me know if this is possible. Thank you!

[patbyr]

Thank you for your quick respond on this but this didn't solve what I was looking for. I have seen several forums that explains how to set up running total, which is the easy part, but I haven't seen any forum that explains how to reset it by group with a conditional column to indicate that it's reset. Would love to see if we could do this in Power Bi but the only option that I have is to do it in excel then have Power bi pull that data to set up the front end. If anyone can make this possible in power bi, please do let me know.

Thanks.

[Anonymous]

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]

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.

[patbyr]

I have read that forum before but didn't see how that will work for me. I even took your advise and tried to see if it works. Not sure what exactly I'm doing wrong here but I did add new column and copied it to change it to match the tables I have in it. This is what I have in my power bi. (See picture below) Looks like I have one of the values or format wrong. Pleaes advise.

[Greg_Deckler]

@patbyr Sounds like Cthulhu: Cthulhu - Microsoft Power BI Community