SharePoint Document Issues

Calculate SharePoint Order Totals by Invoice with Power Automate

Emma BrownEmma Brown Nov 4, 2026 868 views

Question details

The user needs to calculate the total order amount for each specific invoice by aggregating multiple amounts from a SharePoint Orders list into an Invoices list.

Calculate SharePoint Order Totals by Invoice Using Power Automate
Product
SharePoint / Power Automate
Device & OS
not provided
Scenario
Aggregating and calculating order totals for corresponding invoices across related SharePoint lists.
Observed behavior
The standard Total function is unavailable for lookup or calculated columns in the required cross-list manner, necessitating a custom flow.
Before you start

Ensure you have the necessary edit permissions for both the Orders and Invoices SharePoint lists, and that you have access to create flows in Power Automate for your Microsoft 365 environment.

Solution 1Recommended

Aggregate Amounts Using Power Automate 'Apply to each' Loop

Build a flow in Power Automate to retrieve matching order items, convert amounts to numbers, and sum them up using a loop.

Since SharePoint calculated columns cannot aggregate data across different lists using lookup columns, Power Automate provides a reliable way to query the related orders and calculate the total dynamically.

This flow will search the Orders list for items matching the current Invoice ID, add the amounts together, and update the Invoice list with the final total.

1
Initialize a Variable for the Total

Create a new Power Automate flow. Add an 'Initialize variable' action, name it 'TotalAmount', set the type to 'Float', and leave the initial value blank or set it to 0.

2
Get Related Order Items

Add a 'Get items' action pointing to your SharePoint Orders list. Use the OData Filter Query field to filter the orders where the Invoice lookup column matches the specific Invoice ID.

3
Apply to Each Loop

Add an 'Apply to each' control and select the 'value' dynamic content from the 'Get items' action to iterate through the retrieved order records.

4
Increment the Total Variable

Inside the loop, add an 'Increment variable' action. Select the 'TotalAmount' variable and pass the 'Amount' dynamic content from the order item. Use an expression to convert it to a float if necessary: float(item()?['Amount']).

5
Update the Invoice List

Outside the loop, add an 'Update item' action pointing to your Invoices list. Update the Total field with the final value of the 'TotalAmount' variable.

Aggregate Amounts Using Power Automate 'Apply to each' Loop
Automation Tip: You can set this flow to trigger automatically using 'When an item is created or modified' on the Orders list to keep your Invoices list constantly updated.
Free Microsoft Office alternative

Manage Your Spreadsheets Easily with WPS Office

While Power Automate handles complex SharePoint list logic, you can easily manage, calculate, and analyze exported invoice data locally using WPS Spreadsheet. It's a lightweight, fully compatible alternative to Microsoft Excel.

Seamlessly open, edit, and save Microsoft Excel (.xlsx) format files with perfect compatibility.Built-in advanced formulas like SUMIF and VLOOKUP to easily calculate cross-sheet invoice totals.Completely free and lightweight Office suite with a familiar user interface.Rich set of templates for invoices, accounting, and order management.
microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use a standard calculated column to sum lookup values in SharePoint?

SharePoint calculated columns are strictly limited to calculating values within the same row of a single list. They cannot reference lookup columns or perform aggregation (like summing) across multiple lists. You must use Power Automate or custom development for cross-list calculations.

How do I handle empty amount fields in my Power Automate loop?

You can add a 'Condition' action inside your 'Apply to each' loop to check if the amount field is null or empty before attempting to increment the total variable. This prevents the flow from failing due to empty data.

Can I trigger this Power Automate flow automatically when a new order is added?

Yes, you can use the 'When an item is created or modified' trigger in Power Automate attached to the Orders list. This ensures the invoice total is recalculated automatically whenever order data changes.