Answered

Help with sum formula for enahnced gallery details

(0) Share

Report

Posted on by CarlHRVA

601

I am trying to provide a little bit of "reporting" from my gallery to my users. I've been able to set up dynamic lables which display the total amount, the max amount and the average amount due for all visible items in the gallery. I'd like to take it a step further and display the total amount based on the "Aging Category". In my app we have 6 different aging categories ( "Current," "31-60," "61-90," "91-120," "121-150," "Over 150,") and I would like to display the total balance for each in a dynamic label, just like I was able to do with the totals. I've tried using an If statement to filter based on aging category but I've had no success so far. Do I need to use a collection and group them first? My formula is a complete mess at this point and definitely doesn't work but below is what it looks like Thank you for any and all help.

Sum(If(AGING_x0020_CATEGORY = "Current", UpdateContext:({TotalCurrent: 'BALANCE AMOUNT'}),'Total Current')

Categories:

Building Power Apps

I have the same question (0)

All responses (14)

Answers (1)

wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!
Hey @CarlHRVA, you could use GroupBy and a collection if you like but one technique I like to use is summing directly from the gallery. You can filter the summed items from within the gallery and then any additional filtering trickles down into those sums (if that makes sense). Here's what it looks like (assuming I understand your categories and field names):

// Put each of these in the text property of a label // Current amount Sum( Filter( Gallery.AllItems.'BALANCE AMOUNT', AGING_x0020_CATEGORY = "Current" ), 'BALANCE AMOUNT' ) // 31-60 amount Sum( Filter( Gallery.AllItems.'BALANCE AMOUNT', AGING_x0020_CATEGORY = "31-60" ), 'BALANCE AMOUNT' ) // 61-90 amount Sum( Filter( Gallery.AllItems.'BALANCE AMOUNT', AGING_x0020_CATEGORY = "61-90" ), 'BALANCE AMOUNT' ) // ...and so on for each category

Of course, replace "Gallery" with the name of your gallery and adjust the field names if I didn't get them correct. That should give you a summation of the balance amount for each group in the gallery, which again responds to any filtering applied to that gallery, making for a fairly dynamic experience. Feel free to respond if I need to clarify anything or can try to assist further!

Was this reply helpful? Yes No
CarlHRVA 601 on at

Like (0)

Report
Copy link

Link copied!

@wyotim Thank you so much for your help! I've tried implementing the first formula but still receiving an error...

Was this reply helpful? Yes No
CarlHRVA 601 on at

Like (0)

Report
Copy link

Link copied!

This is what the two fields in my gallery that display those items text property looks like...

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!
Maybe try the following:

// Current amount Sum( Filter( Gallery.AllItems.'BALANCE AMOUNT', 'AGING CATEGORY' = "Current" ), 'BALANCE AMOUNT' )

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!

Also, make sure you aren't missing a comma after the Gallery.AllItems.'BALANCE AMOUNT' section. It may be looking for an operand due to that.

Was this reply helpful? Yes No
CarlHRVA 601 on at

Like (0)

Report
Copy link

Link copied!

@wyotim Getting closer, thank you so much! I was missing the comma and since the data source is a SharePoint list I opened the list and sorted each column and pulled the column name from there. What I end up with is a formula that is nearly correct but it says the "=" sign is an invalid argument type now?

Was this reply helpful? Yes No
CarlHRVA 601 on at

Like (0)

Report
Copy link

Link copied!

I've tried using AGING_x0020_CATEGORY.Value and ManagersUIGallery.AGING_x0020_CATEGORY.Value and it doesn't like either of those.

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!

What data type is AGING_x0020_CATEGORY? Unfortunately, I don't know SharePoint too well (the company I work for uses SQL primarily) but there may be some issue with the type, like if it is a Choice type instead of a text type. I'll look for what that may be real quick.

Was this reply helpful? Yes No
CarlHRVA 601 on at

Like (0)

Report
Copy link

Link copied!

It's a single-line of text column. I tried changing it to a choice column as well but that didn't work and also caused the label in my gallery to slop working as well.

Was this reply helpful? Yes No
wyotim 2,545 on at

Like (1)

Report
Copy link

Link copied!

That is odd. I know that there are/were issues with Choice type columns not being filter-able in PowerApps but I thought text fields would work roughly the same. Perhaps they don't. I will do some more checking to see what I can find.

Was this reply helpful? Yes No