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
patbyr's avatar
patbyr
Frequent Visitor
3 years ago

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'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.