cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmegow
Helper II
Helper II

sumif

Hello Power Apps Community, I have achieved half of my goal with assistance from several other posts.

 

I have a 'FPC-Purchase Orders' List and an 'FPC-Invoices' List.  I need to create a column that reports the total amount of Invoices paid against each Purchase Order.  I've been able to create a gallery which does the filtering and the summation that I need, where I'm falling short is how to turn the sum of the gallery into a currency column.  The reason I need this column is because when an item is added to the Invoice List, I have a Flow that creates a cover page (along with the invoice attachment) that is sent to accounting to actually pay the invoice.  Accounting has asked that we add the previously expended amount to the cover page with each invoice.  If I can calculate the amount prior to submitting the item to the list, the amount should be correct.

 

cmegow_0-1614442611685.png

 

In this view, the expended amount is a label which the formula:  

 

Sum(Gallery1.AllItems,'Invoice Amount')

 

The Gallery1 Items formula is:

 

Filter('FPC-Invoices','Purchase Order Number' = 'PO Number_DataCard1'.Default) 

 

Your guidance is greatly appreciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

Hi @cmegow ,

You can create a column on the fly using AddColumns()  and the formula you have - you could also possibly use GroupBy() - how you implement what and where depends a lot on your data structure and your final outcome needs.

I assume an invoice may have one or more purchase orders against it - and each PO may have one or more historical billings?  I'm also guessing PO's are assigned against a budget, so Finance are likely wanting to track the consumption of that PO against its allocated budget - hence the need to track historical spend-to-date against each PO?

So the main question would be - where exactly do you want this column?  If your PO numbers have a 1-many relationship with PO activities, then the logical location would be at the PO level.  If your list just has all the PO activity in one place (PO entries for the same PO but different dates and amounts) then this becomes a little harder as it would make sense to try and aggregate the historical spend at a higher level - which might now be the invoice level.  If your invoices can have multiple PO's, this then gets again more challenging.

Can you share more info on the data model or perhaps an example of the invoice output, and where/how you would like to display this sum?  

Kind regards,

RT

View solution in original post

2 REPLIES 2
RusselThomas
Microsoft
Microsoft

Hi @cmegow ,

You can create a column on the fly using AddColumns()  and the formula you have - you could also possibly use GroupBy() - how you implement what and where depends a lot on your data structure and your final outcome needs.

I assume an invoice may have one or more purchase orders against it - and each PO may have one or more historical billings?  I'm also guessing PO's are assigned against a budget, so Finance are likely wanting to track the consumption of that PO against its allocated budget - hence the need to track historical spend-to-date against each PO?

So the main question would be - where exactly do you want this column?  If your PO numbers have a 1-many relationship with PO activities, then the logical location would be at the PO level.  If your list just has all the PO activity in one place (PO entries for the same PO but different dates and amounts) then this becomes a little harder as it would make sense to try and aggregate the historical spend at a higher level - which might now be the invoice level.  If your invoices can have multiple PO's, this then gets again more challenging.

Can you share more info on the data model or perhaps an example of the invoice output, and where/how you would like to display this sum?  

Kind regards,

RT

View solution in original post

cmegow
Helper II
Helper II

@RusselThomas thank you for your input.  I was able to work in the system again tonight and using your guidance fixed the issue.  My problem was certainly that I was performing my calculation in the wrong place.  I created the gallery in the invoice list and calculated the sum of all visible items.  Thank you for challenging me to see and think about the solution differently.  My accounting department will starting receiving the information that have requested tomorrow!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (76,574)