topic Re: How do I find keep a running total until it hits the max size or less. Otherwise it needs to restart in DAX Commands and Tips

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

525380 — Mon, 24 Apr 2023 01:13:39 GMT

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

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

Wilson_ — Mon, 24 Apr 2023 14:28:20 GMT

Hello,

Can you please share your pbix file?

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

525380 — Mon, 24 Apr 2023 15:23:47 GMT

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.

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

Wilson_ — Mon, 24 Apr 2023 16:09:42 GMT

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

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

525380 — Mon, 24 Apr 2023 16:37:49 GMT

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

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

Wilson_ — Wed, 26 Apr 2023 12:05:10 GMT

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

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

525380 — Thu, 27 Apr 2023 14:43:09 GMT

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.