Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

Help with sum formula for enahnced gallery details

(0) ShareShare
ReportReport
Posted on by 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 Smiley LOL Thank you for any and all help.

 

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

Categories:
  • CarlHRVA Profile Picture
    CarlHRVA 601 on at
    Re: Help with sum formula for enahnced gallery details

    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. Smiley Very Happy

  • wyotim Profile Picture
    wyotim 2,538 on at
    Re: Help with sum formula for enahnced gallery details

    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!

  • wyotim Profile Picture
    wyotim 2,538 on at
    Re: Help with sum formula for enahnced gallery details

    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! 

  • Verified answer
    CarlHRVA Profile Picture
    CarlHRVA 601 on at
    Re: Help with sum formula for enahnced gallery details

    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 🙂

  • wyotim Profile Picture
    wyotim 2,538 on at
    Re: Help with sum formula for enahnced gallery details

    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.

  • CarlHRVA Profile Picture
    CarlHRVA 601 on at
    Re: Help with sum formula for enahnced gallery details

    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.

  • wyotim Profile Picture
    wyotim 2,538 on at
    Re: Help with sum formula for enahnced gallery details

    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.

  • CarlHRVA Profile Picture
    CarlHRVA 601 on at
    Re: Help with sum formula for enahnced gallery details

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

  • CarlHRVA Profile Picture
    CarlHRVA 601 on at
    Re: Help with sum formula for enahnced gallery details

    @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?

     

    Capture.PNG

     

  • wyotim Profile Picture
    wyotim 2,538 on at
    Re: Help with sum formula for enahnced gallery details

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,526

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard