topic Re: How do I create a column that adds a rolling total until it hits a specific max value? in DAX Commands and Tips

How do I create a column that adds a rolling total until it hits a specific max value?

525380 — Wed, 19 Apr 2023 00:17:06 GMT

I would greatly be appreciative if someone could help.

I want to create a stacked graph that adds the value (team#) until it hits a specific maximum team# that I've specified in a different column. Each time it is adding the rolling total it will say Period 1, when it hits the max, it will then start the total again until it hits the max and will say Period 2. this happens until the max team # has been hit.

This is the initial data:

Item ID	Item Size	Max Size per Period
1	10	30
2	20	30
3	10	30
4	5	30
5	15	30
6	10	30
7	16

I need in DAX formula to calculate Running Total of Item Size and Period Name so I can add to the bar graph and show the distribution like this.

Item ID	Item Size	Running Total of Item Size	Period Name	MaxSize per Period
1	10	10	Period 1	30
2	20	30	Period 1	30
3	10	10	Period 2	30
4	5	15	Period 2	30
5	15	30	Period 2	30
6	10	10	Period 3	30
7	16	26	Period 3	30

Re: How do I create a column that adds a rolling total until it hits a specific max value?

Anonymous — Thu, 20 Apr 2023 02:09:51 GMT

Hi 525380

You can refer to the following measures

1.Create a mesaure to calculate the remainder of the sum

Remainder = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Item ID] <= SELECTEDVALUE ( 'Table'[Item ID] ) ), [Item Size] ) VAR b = CALCULATE ( SUM ( 'Table'[Max Size per Period] ), 'Table'[Item ID] = SELECTEDVALUE ( 'Table'[Item ID] ) ) RETURN MOD ( a, b )

2.Then create a measure to create the rolloing total based on the reminder measure

Running Total = VAR a = COUNTROWS ( FILTER ( ALL ( 'Table' ), [Item ID] < SELECTEDVALUE ( 'Table'[Item ID] ) && [Remainder] = 0 ) ) VAR b = CALCULATE ( SUM ( 'Table'[Max Size per Period] ), 'Table'[Item ID] = SELECTEDVALUE ( 'Table'[Item ID] ) ) RETURN SUMX ( FILTER ( ALL ( 'Table' ), [Item ID] <= SELECTEDVALUE ( 'Table'[Item ID] ) ), [Item Size] ) - b * a

3.Create a measure to display size period based on Running Total measure

Size Period = VAR a = CALCULATE ( SUM ( 'Table'[Max Size per Period] ), 'Table'[Item ID] = SELECTEDVALUE ( 'Table'[Item ID] ) ) RETURN "Period" & COUNTROWS ( FILTER ( ALLSELECTED ( 'Table' ), [Item ID] < SELECTEDVALUE ( 'Table'[Item ID] ) && [Running Total] = a ) ) + 1

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How do I create a column that adds a rolling total until it hits a specific max value?

525380 — Mon, 24 Apr 2023 00:31:10 GMT

How do I make it so the remainder is the max size or less, ELSE start the running total over at 0.

Re: How do I create a column that adds a rolling total until it hits a specific max value?

525380 — Mon, 24 Apr 2023 01:16:50 GMT

Everything works, but it has to =30. I want it to = max size or less, otherwise it needs to re-start the running total and increment the new period or PI.