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')
It would have taken me days to figure that out without your help, thank you so much! This is great and will be a huge benefit to our users.
I went ahead and marked your post as the solution (one of the perks of being a Super User!). Hit me up if I can try to help with anything in the future!
Awesome! Happy that worked out for you! We can mark your post as the solution if you like as you were the one who solved it; I just got the ball rolling.
One thing I was going to mention is that you can use the same technique on items in the gallery, such as labels. It's a pretty useful tool to have and I don't see used all that much.
Great job on getting that working!
I got it! My final formula looks like this:
Text(
Sum(
Filter(
ManagerUIGallery.AllItems,
AGING_x0020_CATEGORY = "Current"
),
'BALANCE AMOUNT'
),
"[$-en-US]$#,###.00"
)
Thank you so much for your help! If you edit your original post I'd like to mark yours as the solution 🙂
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.
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.
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.
I've tried using AGING_x0020_CATEGORY.Value and ManagersUIGallery.AGING_x0020_CATEGORY.Value and it doesn't like either of those.
@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?
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.