How to sum all values from List A based on a lookup column's choice value and send to List B

[e_jones]

Hi all,

I have a list of sales (List A) that contains columns Sale Amount and Commission and a lookup column (List B) "Office Location". Since the Modern experience of Sharepoint does not allow for grouped sum totals OOTB, I want to calculate the revenue of each office's sales (Sale Amount * Commission), sum them, and update that office's list item for Total Revenue in List B with a flow every time a new item is added to List A.

Example:

List A:

Sale Amount  Commission  Office Location

$100                15%               Woodcrest

$150                15%               Riverside

$500                20%               Riverside

List B:

Office Location  Total Revenue

Riverside             $122.50

Woodcrest          $15.00

Any ideas on how to get this to work? I've found this documentation that does something similar, but I'm not exactly sure how to write the expressions or format the Flow. Any help would be greatly appreciated!

[v-yuazh-msft · Accepted solution]

Hi @e_jones,

Do you want to update the item's Total Revenue column value in the List B when an item is created in the List A?

If yes, you could refer to my solution below.

I have made a test on my side to create a List A as below:

The Office Location column is a Lookup column from (List B) "Office Location".

The Sale Amount column is a currency columnas below:

The Commission column is a Number type column show as Percentage as below:

 

I have created a List B as below:

The Office Location column is a Single line of text column.

The Total Revenue column is a Currency column,default value is 0:

You could refer to screenshot below to create the flow:

The expression in the Total Revenue column in the Update item action as below:

add(body('Get_item')?['Total_x0020_Revenue'],mul(triggerBody()?['Sale_x0020_Amount'],triggerBody()?['Commission']))

When an item is created in the List A as below:

The flow would run successfully as below:

The List B would be updated as below:

Please let me know if your problem could be solved.

Best regrads,

Alice

[ControllerFred]

So... I have a related question but I am super new to Microsoft Lists.

I have a source list of a particular Contract.  Award and Modifications.

For Example:

Jan1 Award $1000

Jan 5 Mod    $250

In my Target List I want it to pull the Total value of the Award and Mod.

Further complicating this Target list is that each Contract (source list) will be summarized like this,

Contract A $1250 (lookup field from source list above)

Contract B $1500 (lookup field from unique source list like Contract A source list)

Hopefully one of you bright people can help me.

Thanks,

ControllerFred