Summing a column where condition applies

(0) Share

Report

Posted on by MC-07081004-0

Hi,

I hope someone can help me, fairly new user to power apps, so sorry if this question has been answered before but I could not find a suitable solution.

I am designing a PO system, and I want to sum a column in a data table where a condition is met, and update a different data table with the answer.

Table to sum:

Table Name: PurchaseOrderItems

Column Name to Sum: Line_Vaue_£

Condition Column Name: PurchaseOrders

Table to update:

Table Name: PurchaseOrder

Column Name to Update: Total Goods Value £

Condition Column Name: PO Number

My app has a purchaseOrders Screen which would have a box to link to the "Total Goods Value £"

1003 should = £1,910.00

1004 should = £15,000.00

PurchaseOrderItems may have multiple entries to sum up

The PO Number column is auto generated in the Purchaseorder Table when you create a new entry

The purchaseOrders column is a drop down you select, linked to the PO Number column

Would I perform this calculation in a box in the App (PurchaseOrders Screen) and send to the PurchaseOrders table or perform in the PurchaseOrders table and link to the Box in the App (PurchaseOrders Screen)??

The App has a button to complete the PO to link to power automate, this could be used to perform this calculation.

Thanks for your help

Mike

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

stampcoin 5,058 Super User 2025 Season 2 on at

Like (0)

Report

@MC-07081004-0 Hi,

Assume you have PurchaseOrderHeader and PurchaseOrderLines.

I suggest that use Dataverse rollup or a Power Automate flow to maintain the value in the table( PO header).

You still have to run line-sum and header in total when the month close.

So, depends on how you want to use it.

Was this reply helpful? Yes No
Suggested answer

WarrenBelz 153,136 Most Valuable Professional on at

Like (0)

Report
Hi @MC-07081004-0,

You do not need a Flow here - if you are choosing a PurchaseOrders value to filter PurchaseOrderItems, then this should update the Table PurchaseOrder with the total of that PO. The structure also addresses any Delegation issues with the Sum function.
With( { _Data: Filter( PurchaseOrderItems, PurchaseOrders = YourChosenValue ) }, Patch( PurchaseOrder, Defaults(PurchaseOrder), { 'PO Number': YourChosenValue, 'Total Good Value £': Sum(_Data, 'Line_Value_£') } ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
MC-07081004-0 4 on at

Like (0)

Report

Hi @WarrenBelz,

Thanks for the suggestion, as a new user of Power apps, I am unsure how to use this code. Where do I add the code you have suggested to work?

Does it go in the form or the table?

Sorry if this a basic question but i am trying to learn

Mike

Was this reply helpful? Yes No
WarrenBelz 153,136 Most Valuable Professional on at

Like (0)

Report

hI @MC-07081004-0,

As per your requirement, it is writing a new record in PurchaseOrder with a 'PO Number' and the total of all Line_Value_£' fields from PurchaseOrderItems matching the PurchaseOrders field selected/entered. You can run it from whatever icon/button that initiates this when required (it is an action, so will be OnSelect of a control).

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
WarrenBelz 153,136 Most Valuable Professional on at

Like (0)

Report

Hi @MC-07081004-0,

A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

Please ✅Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No
MC-07081004-0 4 on at

Like (0)

Report

Hi @WarrenBelz,

Thanks for your help on this, i am now starting to understand the query and what you have done for me, wow thank you, almost perfect. Thanks for your patience

I have two small issues left:

1) - setting the data filter, side 2 is an error:

side 1 =

PurchaseOrderItemses,

PurchaseOrdersNo

Side 2 = ??

a) how do i select my value?

i)I tried adding a card with the value, Datacardvalue6, that had a clash of text v Record

ii)I Tried purchaseorder, PurchaseOrder_No

2) When I ask Copilot to explain the formulae to me, it says it will create a new record - I wanted it to amend a current record? Will this add a new record or amend a current record?

Mike

Was this reply helpful? Yes No
WarrenBelz 153,136 Most Valuable Professional on at

Like (0)

Report
Hi @MC-07081004-0,

I used a new record example as you did not specify in your post what you wanted to do. How you select/enter the value is really up to you, but you could use a Text Input that allows the user to enter the value. Before I can elaborate further, I need to know

the field type of PurchaseOrders (why you are getting the conflict)

the Item of your Form

your data source type (SharePoint etc).

whether you are using Classic or Modern controls.

Was this reply helpful? Yes No
WarrenBelz 153,136 Most Valuable Professional on at

Like (0)

Report

Hi @MC-07081004-0,

A quick follow-up to see if you received the answer you were looking for or if you need further assistance.

Please ✅Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like♥.
Visit my blog Practical Power Apps LinkedIn

Was this reply helpful? Yes No