Storing SharePoint List Column Sum (filtered) in Another SharePoint List

[chasquad2]

Hi Everyone!

I have 2 SharePoint Online Lists:

List One:  Sales

List Two: Profit & Loss Statements

In the Sales list, each sale is listed and there are a couple of columns to note:

• Sales Channel (choice field with pre-set selectable values)
• Gross Sales (calculated field from other columns in this list presented as a number with 2 decimals)

In the Profit & Loss Statements list, there is one list item (item id 1) for this fiscal year and then there is a separate column for each sales channel.  These are the same sales channels used in the Sales list above.

I wanted to use Power Automate to create a flow that would run each time a new item is created in the Sales list that would then do the following:

• Look at the Sales list and determine the total sum of the Gross Sales column by Sales Channel and update the Profit & Loss Statements item id 1 columns for each sales channel with those totals.

Thanks so much for looking at this.  I think that, with your help, figuring this out will help me learn more about the capabilities of Power Automate!

[ChristianAbata]

hi @chasquad2 what you need to do is user the expression length. 

See this expressions could count your total rows.

[chasquad2]

Thanks, but I'm not looking to count the number of rows.  I just need to sum the number value in all rows that match the filtered criteria and then update a specific column in another SharePoint list with that value.

[ScottShearer]

@chasquad2 

I would like to suggest that there is a much easier way to approach this.  Why not just add the values from the new row of data to the existing values in the Gross Sales list?   If you need help approaching it this way, please let me know.

[chasquad2]

Thanks

Are you saying that for each time a new sale is added to the Sales list, that there is a way to take that Gross Sales value for the given Sales Channel and add that value to the value that is already in the Profit & Loss list item record (item id 1) column for that Sales Channel and then replace that value with the new one?

[ScottShearer]

@chasquad2 :

Yes - that is exactly what I am saying.  You would use a Get Item action to retrieve the data.  Use an Update item to update the columns.  In the Update Item action use add() expressions to add the existing value to the value in the new list item.

Give it a try and let me know if you need help.

[chasquad2]

Thanks

I'm struggling with the Update Item section.  So, I would need to add an add() expression to each field (eBay.com, Amazon.com, etc.) that would add the Gross field from the Sales table to the existing value in the appropriate field in the Profit & Loss list for record id 1.  But only if that sales channel is selected when the new sales entry is created. 

[chasquad2]

So, would the expression for eBay.com be:

If the Sales Channel value in the Sales list = eBay.com then add the Gross value in the Sales list to the eBay.com column in the Profit & Loss Statements list?  If so, what would the syntax for this be?

Thanks

[chasquad2]

Any help would be appreciated.  Thanks!