How do I find keep a running total until it hits the max size or less. Otherwise it needs to restart

[525380]

I calculate the Remainder:

Remainder =

VAR a =

    SUMX (

        FILTER (

            ALLSELECTED ( 'Epic' ),

            [Epic ID] <= SELECTEDVALUE ( 'Epic'[Epic ID] )

        ),

        [TeamsPI Avg]

    )

VAR b =

    CALCULATE (

        SUM ( 'Epic'[Portfolio Supply] ),

        'Epic'[Epic ID] = SELECTEDVALUE ( 'Epic'[Epic ID] )

    )

RETURN

  MOD ( a, b )

And then get the running total : 

Running Total =

VAR a =

    COUNTROWS (

        FILTER (

            ALL ( 'Epic' ),

            [Epic ID] < SELECTEDVALUE ( 'Epic'[Epic ID] )

                && [Remainder] = 0

        )

    )

VAR b =

    CALCULATE (

        SUM ( 'Epic'[Portfolio Supply] ),

        'Epic'[Epic ID] = SELECTEDVALUE ( 'Epic'[Epic ID] )

    )

RETURN

    SUMX (

        FILTER ( ALL ( 'Epic' ), [Epic ID] <= SELECTEDVALUE ( 'Epic'[Epic ID] ) ),

        [TeamsPI Avg]

    ) - b * a

When the Running Total hits 30 a line is printed out:

PI =

VAR a =

    CALCULATE (

        SUM ( 'Epic'[Portfolio Supply] ),

        'Epic'[Epic ID] = SELECTEDVALUE ( 'Epic'[Epic ID] )

    )

RETURN

    "PI "

        & COUNTROWS (

            FILTER (

                ALLSELECTED ( 'Epic' ),

                [Epic ID] < SELECTEDVALUE ( 'Epic'[Epic ID] )

                    && [Running Total] = a

            )

        ) + 1

This all works exept that it has to = 30(the max size). I want it to hit the max size or less, otherwise it starts the running total again and prints out new PI(period). 

[Wilson_]

Hello,

Can you please share your pbix file?

[525380]

How do i attach my PBIX to this post? I looked at all the buttons and don't see an attach file or pbix. I see attach media, link pic, etc., but none to upload the PBIX. 

[Wilson_]

You can upload it somewhere (ex: Google drive or Dropbox) and share the link to the file.

[525380]

https://drive.google.com/file/d/1YeAPHcMVgMrtjhyHtZiyOSjXqWKNIQ8f/view?usp=share_link 

[Wilson_ · Accepted solution]

Hm, I'm unfortunately stumped at the moment after looking at it for a while. The closest I got was using a cumulative total and the quotient in the PI measure, which at least solves your issue running total not resetting if the remainder was non-zero. However, that doesn't quite work with your requirement that the running total resets.

PI = 
VAR CumulativeTotal =
CALCULATE (
    SUM ( Epic[TeamsPi Avg] ),
    REMOVEFILTERS ( Epic ),
    Epic[Epic ID] <= SELECTEDVALUE ( Epic[Epic ID] )
)
VAR Divisor = SELECTEDVALUE ( Epic[Portfolio Supply] )
VAR Suffix = IF ( Divisor <> 0, QUOTIENT ( CumulativeTotal - 1, Divisor ) )
VAR Result = "PI " & FORMAT ( Suffix + 1, "0" )

RETURN IF ( ISINSCOPE ( Epic[Epic ID] ), Result ) -- gets rid of the nonsensical Total value

 Accounting for the reset the first time it's with a non-zero remainder is easy; keeping that "memory" has proved more difficult for me, since you can only use one "formula" for the entire "column".

I wish I could be of more help at the moment but the above is the best I have for now. If I figure something else out, I'll let you know. 😄 

[525380]

I appreciate you trying! If you can think of anything let me know. I'm still thinking and maybe need to redo the logic altogether.