How to create a flow that will sum a specific category and send a notification when the amount is greater than the target.

[KJoy17]

Hello, I am new to power automate. I would like to have power automate read my excel file and sum all of the rows that fall into that name and alert me when it is above $1000.

Example:

Account	Date	Amount
AA	2/1/2022	500
AA	2/2/2022	100
BH	2/3/2022	900
BH	2/4/2022	901

Thank you in advance. 

[CraigStewart]

This will get your total

Initialise a variable

Use a apply to each with increment variable

Compose your variable

Using your initial data with a couple more lines added

Result

[KJoy17]

Thank you for the response. Also can you share what you inputted into the integer expression? To be more clear, I would need it to sum only the individual account names such as; AA sum of amount, BH sum of amount, etc. 

[CraigStewart]

Sorry I didn't add that

int(Items('AddTogether')['Amount'])

So that's just summing the whole column at the moment,.

This thread here seems to be what you're after  https://powerusers.microsoft.com/t5/Building-Flows/use-flow-to-sum-some-row-in-excel/td-p/432077 

[KJoy17]

For this formula, "int(Items('AddTogether')['Amount'])" AddTogether gives me an error. I also referenced that link already and I received errors when I followed it step by step. 

There is no other way to sum the grand total for each unique account name?  

[CraigStewart · Accepted solution]

Hi

Sorry for the delay coming back to you, but yes there is a way - if you follow the instructions in the link I posted that will take you to the output of what you want,

I've just worked through it this evening and created a flow similar to this to use the output you're after.

The table i'm testing with looks like this and you can see the totals we expect to see

So we can pick up from the end of guide in the link which will take you Compose 4, which is called

Compose - Create Final String in my flow:

From here I use 

split(outputs('Compose_-_Create_Final_String'),';')

and did the following

We'll use split to get to our data

split(item(),':')?[0]

split(item(),':')?[1]

0 is the first item, 1 is the second. and the result is below

You can ignore variable 6 and Compose 9 they are unrelated.

From here we enter a apply to each

The Output is from Select - Split the Output

The Amount in Condition 3 is items('LoopData')?['Amount'] and this is not equal to null() 

so that we can deal with the blank line at the end of the data.

 We then have 2 Compose pieces items('LoopData')?['Amount'] & items('LoopData')?['Company']

With that data we can then use another condition of 

int(outputs('Compose_-_Amount'))  is greater than int(1000) or whatever your target number is

 As this point I've created a simple compose to show it working, but from here we could send an email, add it to sharepoint list or write it back to another excel sheet or something else.

Hopefully this helps you if you haven't already got yourself sorted.

Cheers

Craig

[KJoy17]

Thank you for the response! The code worked! The only issue I am having is in your LoopData my Select activity did not make a Company or Amount code. I know you said that variable 6 and compose 9 are unrelated and can be ignored, but maybe I do need it? Please let me know if I am missing anything. 

[CraigStewart]

If you type them as expressions

items('LoopData')?['Company']

items('LoopData')?['Amount']

 it should pick them up

[KJoy17]

Apologies for the delay, I got everything to work like you said. When the flow runs, it only provides 1 company instead of it returning more than one if it is greater than 600. How can I have it return multiple and send out an email for each one?

[CraigStewart]

Hi

No worries. Can you post your flow from the point of compose final string and let's take a look.

[KJoy17]

Thank you for all of your help I got the email flow to work alongside the flow you have!