topic Re: Conditional Running Total with DAX in DAX Commands and Tips

Conditional Running Total with DAX

apalacios — Mon, 07 Feb 2022 12:40:28 GMT

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

Re: Conditional Running Total with DAX

amitchandak — Mon, 07 Feb 2022 12:45:02 GMT

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

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

Re: Conditional Running Total with DAX

apalacios — Mon, 07 Feb 2022 12:46:44 GMT

The column Conditional Running Total dont exists....

Re: Conditional Running Total with DAX

v-zhangti — Mon, 14 Feb 2022 08:51:13 GMT

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.