Forum Discussion

apalacios's avatar
apalacios
Frequent Visitor
4 years ago
Solved

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 sho...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

v-zhangti's avatar
v-zhangti
Community Support
4 years ago

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.