Total of a calculated column on Sharepoint 365 and MS Teams list

Question

I looked around but couldn't find an answer related to the new modern lists, most is of for classic sharepoint lists. I hope someone can help me.

I have a list of reservations that has columns for installment payments, which are recorded in 5 separate columns which are then summed in a calculated column called PAYMENTS TOTAL. Now I need to have a sum of all reservations at the bottom or top of the calculated column and it would be great if it would work within MS Teams. If not, Sharepoint page would also work.

I'm not an IT professional but a self-taught IT enthusiast but would love to get this to work and am willing to put some work into it if someone can point me into the right direction.

Any help will be much appreciated!

Answer 1

Hi @allyazz

What are the 5 separate columns you are using?

I recommend you to use the number column, which has a built-in sum function. Other types of columns do not have this function.

You can find the totals option in the small arrow next to each column.

The problem we are encountering is that the column you need to sum is a calculated column, and it does not have a sum option.

As a workaround, we can create a simple flow to automatically copy the value of the calculated column to another new number column, so you can see the sum of this column.

You can follow the steps below:

1.Create a new number column, in my test it is called test total.

2.Create a flow, when a new item in the list is created or modified, flow will run automatically, and it will assign the value of the calculated column to the newly created number column

The function in the test sum is set like this, select the concat function, and then select PAYMENTS TOTAL:

You can check the result:

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You get an infinite loop because the trigger is set to "When item is created or modified". If it was set only to "When item is created" (just as in the PoC which was provided by vxxx) you wouldn't have any problem with the loop.

Answer 3

Hi @allyazz

I see you haven't had a response to this - probably because the user who provided you with the solution above you didn't actually build this PoC himself and failed to mention a few things.

The Calculated Column total was a feature that was included in previous versions of SP but was removed and was no longer out-of-the-box in new releases.

The best way to get a feature back or added to the product road map is to submit to the UserVoice forums. If it gets enough upvotes it has a chance of being included in a future release. As such, my first recommendation would be to up vote the existing ideas to hopefully spur MS into bringing this useful feature back.

https://sharepoint.uservoice.com/forums/329214-sites-and-collaboration/suggestions/15815590-totals-for-calculated-fields-in-sharepoint-lists

Secondly, the PoC you're seeing above was actually a quick and dirty approach written by me after finding no solution elsewhere - and the ones I did find were advising to do all sorts in SP designer which seemed more convoluted than it needed to be.

The original PoC was posted on my blog - https://veeeetzzzz.medium.com/sharepoint-lists-totals-of-calculated-columns-proof-of-concept-713a71af99f8

In your latest post - I can see you've set up a manual trigger, I built this on the basis that there would be user triggered event so I wouldn't be able to comment further on this without knowing your business process. As for the 37k runs - no idea what variables you've set up but interrogating your Power Automate flow to see what records are being updated will help you trace the error. I have had lots of issues with variables not displaying, sometimes it's a set up issue - sometimes it's a local browser issue with something like cached data. Hard to say without end user observation.

If you would like further support on this it's best to pursue this as a piece of development work with SP/PA - so start posting your scripts, error messages etc and perhaps the SP/PA experts can assist you further.

I wasn't really expecting anyone to use this as a long term solution so further development on this was never the plan and is certainly not recommended as best practice - I had just hoped it'd be used as a stop-gap until the feature was re-instated.

Answer 4

Hi! It worked!! But I have a question, how can I make the formula work with the information already on the list?

Answer 5

Dear @Itch Sun-MSFT

I'm having a problem with PA flow that I have created.

1) I'm seeing a big amount of flow runs, I'm even seeing a warning in the flows run history to turn my flow off if I don't reduce the number of flows. Any ideas why?
2) I also have multiple failed flows due to the Save Conflict. I googled a bit and found recommendations to add a delay. Now, I can understand how this would work, but in our case, the item edit form can sometimes be open for a longer period of time.
3) Is there a trigger that would run after the edit form for an item is closed?

I hope you can find some time for my request, it would be greatly appreciated!