Forum Discussion

androo's avatar
androo
Icon for Advocate III rankAdvocate III
3 years ago
Solved

Conditional cumulative sum as new calculated column in datamodel

In the table below my desired result is the final column (it looks horrid here but pastes nicely enough into Excel). Index:Reference:PrevRowRef:Amount:Cumulative Amount:Desired Cumulative Result: ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    androo 

    is this what you want?

    Column = sumx(FILTER('Table','Table'[Reference]=EARLIER('Table'[Reference])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Amount])+0

     

    pls see the attachment below

androo's avatar
androo
Icon for Advocate III rankAdvocate III
3 years ago

Thanks very much for this neat solution which runs quickly over my almost 500k lines. I did try using the SUMX but I don't think I've ever got one to work in the way I expected or thought it would and this was no exception. In the past I've generally given up with them but have always been able to find some other way of doing whatever I was trying to do. However, this has brought me some understanding of what it is they are doing and so perhaps I will now find a way to use one and avoid repeating some of my more elaborate past workarounds!