web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Adding previous row to...
Power Apps
Answered

Adding previous row total with next row in Sharepoint Gallery

(1) ShareShare
ReportReport
Posted on by Community Power Pla... Microsoft Employee

Hi there.

 

So I had a long message ready but it was deleted for some reason when i typed "post".

 

So here's a shorter version.

 

I have an attendance app and wish to add the number of attendants in each line and then add numbers from previous lines.

Each line consists of following:

 

PlusOne (Significant others)

PlusKids (0-4 kids per)

Employee (Always 1 because it counts the person who signed up)

 

Each event has a MaxAttendants that when reached changes all lines to red where the number is higher than the number of allowed attendants.

 

Here is a table example to show you what i mean

Max attendants for event is 20

NamePlusOnePlusKidsEmployeeTotal
Andy11012
Andy21115
Andy31219
Andy403113
Andy504118
Andy600119
Andy701121
Andy811123
Andy901125

 

I am using sharepoint as data source and the list is called "EventAttendants"

 

Any ideas how to count the total like in the table?

I am hitting my head against a wall trying to figure it out.

 

Thank you in advance

Categories:
Building Power Apps
I have the same question (0)
  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @Anonymous 

    You could take a couple of approaches to this.  Personally I don't like seeing calculated values in a table like this because, what happens if one of the records in the "chain" of totals is altered or deleted?  Now you have the challenge of trying to figure out how to update all the rest of the totals.

    But, that said, these are some options:

    1) Find the last record in your list with the Total and then add your increment to that Total for the new total.  This would be done by Sorting your Filtered results of the list in "Created" descending order and then looking at the first result from that and then adding your new value.

    2) You could do your sum totals in PowerApps instead and abandon the total column in your list.  This would simply be done by using the Sum function across the three columns you have and then summing those totals.

     

    Not sure if you were looking for a formula or some design concepts...so for now, some design concepts.

     

    I hope this is helpful for you.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    @RandyHayes 

    I am sorry for misguiding you with the table. I am already doing your suggestion 2)

     

    However - i am having difficulties with the SUM part. 

    SUM itself isnt a hard thing to do, but getting it to sum a line and then adding the sum of previous lines is a bit harder. 

     

    I have attached a picture showing what i am actually trying to do.

     

    ABK_0-1605771594048.png

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous ,

     

    For sure, this can be done, just one condition, you need to find a way to sequence these records, otherwise, when we use the sum function, we won't find a way which records we should sum. For example, you want to sum according to id, then you probably need to sort them by id, otherwise, the total value might be wired if you sort by other columns.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous 

    Every event has its own ID called MainIDSignup that is paired with all participants.

     

    But i am really unsure as to how the code would look for this. 

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous ,

     

    First, you can create a gallery to show the name, plusone, pluskids, and employee, of course, create a label to show the total.

    And the formula for the label text can be:

    Sum(Filter(EventAttendants,ID<=ThisItem.ID),PlusOne)+Sum(Filter(EventAttendants,ID<=ThisItem.ID),PlusKids)+Sum(Filter(EventAttendants,ID<=ThisItem.ID),Employee)

    And remember, your gallery items should be sorted by ID.

     

    Or you can depend on the register time when the attendees tell you how many people will attend your event (in this case, you need to modify the formula to):

    Sum(Filter(EventAttendants,RegisterTime<=ThisItem.RegisterTime),PlusOne)+Sum(Filter(EventAttendants,RegisterTime<=ThisItem.RegisterTime),PlusKids)+Sum(Filter(EventAttendants,RegisterTime<=ThisItem.RegisterTime),Employee)

    And for your collection, please make sure you filter event by event, you don't want to add all the events attendees to this table.

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous 

     

    Thank you for the suggestion, but it isn't exactly when i was trying to say 🙂

     

    Below is your code edited to suit the actual names in my PowerApps. Being from Denmark some of the names I told here was translated 🙂

     

    Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 PlusOne
) + Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 PlusKids
) + Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 Deltager
)

     

    At the moment this gives the number 6 on each registered user, because there's a total of 6 signed up to the event.

    What I am trying to get is to get a running number total of signups.

     

    Below i have added another picture to try and show what i want with blue numbers - the red is the SUM code you posted.

    Line 1 - 1 person added -  total 1

    Line 2 - 4 persons added - total 5

    Line 3 - 1 person added - total 6 

     

    I am trying to get the total to change for each line... It is not normally showed in the app, but we are going to use it to show when an event is full and when people are on hold for a vacant spot.

     

    ABK_0-1605779699972.png

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous ,

     

    Can you test on only one column, as it works in my gallery, so let's say, you pick employee:

    Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 Deltager
)

    If this gives you 1 on the first line, 2 on the 2nd line, and 3 on the 3rd line, then it means it works, the only problem might be several sums together, in that case, the formula can be changed:

    (Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 PlusOne
)) + (Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 PlusKids
)) + (Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) <= Value(ThisItem.MainIDSignup)
 );
 Deltager
))

    Please let me know if it works.

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous 

    I got the result of 3 on each line sadly.

     

    Sadly nothing changed on my end. 

     

    As a reference - here is my Sharepoint List

     

    ABK_0-1605781049183.png

     

  • Verified answer
    Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous ,

     

    Now I see, you should use SignupID instead of MainIDSignup, then the code should be:

    Sum(
 Filter(
 Deltagerliste;
 Value(SignupID) <= Value(ThisItem.SignupID)
 );
 PlusOne
) + Sum(
 Filter(
 Deltagerliste;
 Value(SignupID) <= Value(ThisItem.SignupID)
 );
 PlusKids
) + Sum(
 Filter(
 Deltagerliste;
 Value(SignupID) <= Value(ThisItem.SignupID)
 );
 Deltager
)

     

    Please try again. This time it should work.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous 

     

    You were partially right in using SignupID. 

    I had to use both so the code ended up being as follows

     

    Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) = Value(ThisItem.MainIDSignup) && Value(SignupID) <= Value(ThisItem.SignupID)
 );
 PlusOne
) + Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) = Value(ThisItem.MainIDSignup) && Value(SignupID) <= Value(ThisItem.SignupID)
 );
 PlusKids
) + Sum(
 Filter(
 Deltagerliste;
 Value(MainIDSignup) = Value(ThisItem.MainIDSignup) && Value(SignupID) <= Value(ThisItem.SignupID)
 );
 Deltager
)

     

    Thank you very much for your help.

    I now have a code working that I am able to use to colorcode participants depending on the amount.

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

News and Announcements

Welcome to the Power Platform Communities
🚀 Season of Sharing Community Challenge Launch!
Ask A Community Pro - Series II

Quick Links

Season of Sharing Community Challenge Launch!

Jump in, show your community spirit, and win prizes!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Valantis Profile Picture

Valantis 474

#1
Valantis Profile Picture

Valantis 474

#3
WarrenBelz Profile Picture

WarrenBelz 375 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans