Conditional Running Total with DAX

[apalacios]

I have a table with 2 columns: ID and Numbers; and I want to create a new column Result with the running total of the Numbers column but every time the running total surpass 20, the running total should be restarted. Also, if the running total is lower than 20, the result should not be displayed. This have to be done using DAX only.

Thanks in advice.

Note: the Conditional Running Total column exist just to show how the running total works.

ID Numbers ConditionalRunningTotal Result

ID	Numbers	ConditionalRunningTotal	Result
1	9	9
2	7	16
3	9	25	25
4	8	8
5	9	17
6	9	26	26
7	1	1
8	0	1
9	0	1
10	9	10
11	9	19
12	2	21	21
13	1	1

[v-zhangti · Accepted solution]

Hi, @apalacios 

I haven't thought of an easier way yet, the following method can achieve your needs first.

Column:

Sum = SUMX(FILTER('Table',[ID]<=EARLIER('Table'[ID])),[Numbers])

Sum 1 = IF([Sum]<20,[Sum],IF([ID]=CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum]>20)),[Sum],BLANK()))

Sum 2 = SUMX(FILTER('Table',[ID]<=EARLIER('Table'[ID])&&[ID]>CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum]>20))),[Numbers])

Sum 3 = IF([Sum 2]<20,[Sum 2],IF([ID]=CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum 2]>20)),[Sum 2],BLANK()))

Sum 4 = SUMX(FILTER('Table',[ID]<=EARLIER('Table'[ID])&&[ID]>CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum 2]>20))),[Numbers])

Sum 5 = IF([Sum 4]<20,[Sum 4],IF([ID]=CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum 4]>20)),[Sum 4],BLANK()))

Sum 6 = SUMX(FILTER('Table',[ID]<=EARLIER('Table'[ID])&&[ID]>CALCULATE(MIN('Table'[ID]),FILTER('Table',[Sum 4]>20))),[Numbers])

ConditionalRunningTotal = IF([Sum 1]<>BLANK(),[Sum 1],IF([Sum 3]<>BLANK(),[Sum 3],IF([Sum 5]<>BLANK(),[Sum 5],[Sum 6])))

Result = IF([ConditionalRunningTotal]<20,BLANK(),[ConditionalRunningTotal])

Best Regards,

Community Support Team _Charlotte

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

[amitchandak]

@apalacios , if ConditionalRunningTotal is a measure then try  a measure like

if([ConditionalRunningTotal]<20, blank(), [ConditionalRunningTotal])

[apalacios]

The column Conditional Running Total dont exists....