Forum Discussion
Conditional Running Total with DAX
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 |
| 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 |
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.
3 Replies
apalacios , if ConditionalRunningTotal is a measure then try a measure like
if([ConditionalRunningTotal]<20, blank(), [ConditionalRunningTotal])
The column Conditional Running Total dont exists....
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.
