Unanswered

Power Automate Calculate Sum Based on ID Array Filter

(0) Share

Report

Posted on by JD-26060753-0

Hi All - I have a currency column in SharePoint List called Total Cost. In Power Apps i want to Calculate Sum of Total Cost based on ID Filter. The ID's could be dynamic array like at times it could be [1,3,4,5] and sometimes the ID array could be different. Sum in PowerApps is non delegable. So we would need to rely on powerautomate.

The power automate is looping through each of the ID's and calculating the sum in the variable within the apply to each action. If there are 20 ID's each iteration is taking around 1s increasing the time taken to 20s for the flow to complete., which is not ideal from performance stand point.

If we include Concurrency the variable to calculate sum cannot be inside the apply to each action.

The other alternate we can think of is using CAML Query. Are there any other better ways to this.

Thanks!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (0)

Report
Hi

If you can pass the list or array of ID's to Power Automate, you could try the following tutorial:
Instantly sum an array with Power Automate by Paul Murana.

Prep your data:

We need to Select only the SharePoint IDs from the IDList array . The Odata contains() function, which I wanted to use, doesn't work here, so try the following method (unless someone knows a better method):

concat('ID eq ', join(variables('IDList'), ' or ID eq '))

This should give us a filter query to select the only the SharePoint IDs from the ID list, as we see in the runtime output:

Select the column that you need to sum. Switch the Select mode to Text mode by clicking the [T] icon:

In the Map, specify the column you want to sum. Use the internal name of the column, you want to sum, see: How to Find the Internal Name of a SharePoint Column (Step-by-Step Guide) – Ellis Karim's Blog

Then follow the tutorial the rest of the tutorial at: Instantly sum an array with Power Automate

Hope this helps.

Ellis Karim
Blog | LinkedIn | Bluesky
If this solved your issue, please mark it as ✅ Accepted Answer. If it helped, feel free to give it a 🩷 Like!

Was this reply helpful? Yes No
JD-26060753-0 21 on at

Like (0)

Report

Thank you for your answer @Ellis Karim! You have pointed me to some good directions.

One question on your approach of building the dynamic filter query "ID eq". The IDs can span more than 100. From what i understand the SharePoint REST API character limit is 260. Will this have an impact if the IDs are more and the filter query crosses 260 limit.

Was this reply helpful? Yes No
Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (0)

Report
(1) I tested the query with a query string length of up to 2,201 characters:

{ "host": { "connectionReferenceName": "shared_sharepointonline", "operationId": "GetItems" }, "parameters": { "dataset": "https://badpassword.sharepoint.com/sites/DemoSite", "table": "fd6444f3-0dd8-4aa3-b539-4cebd236f353", "$filter": "ID eq 128 or ID eq 188 or ID eq 195 or ID eq 217 or ID eq 226 or ID eq 236 or ID eq 244 or ID eq 285 or ID eq 336 or ID eq 372 or\n\ ID eq 385 or ID eq 482 or ID eq 506 or ID eq 592 or ID eq 603 or ID eq 612 or ID eq 632 or ID eq 680 or ID eq 684 or ID eq 726 or\n\ ID eq 833 or ID eq 1090 or ID eq 1109 or ID eq 1141 or ID eq 1304 or ID eq 1321 or ID eq 1351 or ID eq 1352 or ID eq 1410 or ID eq 1417 or\n\ ID eq 1460 or ID eq 1466 or ID eq 1521 or ID eq 1554 or ID eq 1638 or ID eq 1736 or ID eq 1753 or ID eq 1838 or ID eq 1857 or ID eq 1886 or\n\ ID eq 1977 or ID eq 1984 or ID eq 2024 or ID eq 2086 or ID eq 2144 or ID eq 2147 or ID eq 2166 or ID eq 2192 or ID eq 2211 or ID eq 2228 or\n\ ID eq 2244 or ID eq 2294 or ID eq 2365 or ID eq 2409 or ID eq 2451 or ID eq 2474 or ID eq 2490 or ID eq 2542 or ID eq 2546 or ID eq 2606 or\n\ ID eq 2643 or ID eq 2665 or ID eq 2666 or ID eq 2677 or ID eq 2730 or ID eq 2740 or ID eq 2781 or ID eq 2784 or ID eq 2796 or ID eq 3074 or\n\ ID eq 3123 or ID eq 3248 or ID eq 3297 or ID eq 3443 or ID eq 3539 or ID eq 3586 or ID eq 3750 or ID eq 3946 or ID eq 4125 or ID eq 4157 or\n\ ID eq 4316 or ID eq 4340 or ID eq 4384 or ID eq 4450 or ID eq 4517 or ID eq 4524 or ID eq 4668 or ID eq 4710 or ID eq 4741 or ID eq 4778 or\n\ ID eq 4809 or ID eq 4829 or ID eq 4856 or ID eq 4897 or ID eq 4933 or ID eq 5010 or ID eq 5051 or ID eq 5082 or ID eq 5112 or ID eq 5143 or\n\ ID eq 5168 or ID eq 5178 or ID eq 5218 or ID eq 5302 or ID eq 5452 or ID eq 5512 or ID eq 5602 or ID eq 5615 or ID eq 5645 or ID eq 5702 or\n\ ID eq 5738 or ID eq 5746 or ID eq 5857 or ID eq 5864 or ID eq 6005 or ID eq 6111 or ID eq 6137 or ID eq 6145 or ID eq 6147 or ID eq 6156 or\n\ ID eq 6177 or ID eq 6180 or ID eq 6188 or ID eq 6221 or ID eq 6222 or ID eq 6274 or ID eq 6338 or ID eq 6352 or ID eq 6397 or ID eq 6414 or\n\ ID eq 6521 or ID eq 6616 or ID eq 6629 or ID eq 6719 or ID eq 6728 or ID eq 6764 or ID eq 6891 or ID eq 6905 or ID eq 6998 or ID eq 7002 or\n\ ID eq 7029 or ID eq 7056 or ID eq 7156 or ID eq 7173 or ID eq 7287 or ID eq 7300 or ID eq 7305 or ID eq 7308 or ID eq 7332 or ID eq 7392 or\n\ ID eq 7396 or ID eq 7445 or ID eq 7456 or ID eq 7479 or ID eq 7602 or ID eq 7747 or ID eq 7786 or ID eq 7788 or ID eq 7812" } }

You would have to ensure that the query string doesn't exceed 2,201 characters.

(2) What is the size of the SharePoint list? Is it possible to read the entire list (Get Items with Pagination set, and no filter query)? You could then use a Filter array action (followed by the Select actions, then the SUM tutorial). This would be my preferred solution:

Ellis Karim
Blog | LinkedIn | Bluesky
If this solved your issue, please mark it as ✅ Accepted Answer. If it helped, feel free to give it a 🩷 Like!

Was this reply helpful? Yes No