cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChristianStAuby
Frequent Visitor

Cannot Sum Filter items in PowerApps from SharePoint List

Hi I am trying (and struggling) to do a simple SumIf style formula in PowerApps.

 

For an app I have created I have am now required to create a summary screen which per user sums up their data for that day and shows it on screen. Normally in excel this would simply be the most basic formula of a Sumifs but nothing works or produces a viable result or is accurate to the source data!

 

The data is for ferry passenger counting on small boats and the boatmen uploads total numbers of types passengers per trip each time they go. 

SharePoint List  (SP) is called: Boat_Passenger_Numbers

 

PA-1.png

 

The column settings are as follows:

PA-2.png

Adults calculation is: =Adults_Other+Adults_Payers

formatted as a number

Children calculation is: =Children_Other+Children_Payers

formatted as a number

Date-Day calculation is =TEXT(ROUNDDOWN(Created,0),"DD/mm/YYYY")

formatted as Date and Time (Date Only)

The Total_Passengers line is calculated but from within the power app and submitted.

 

What I need to do is for that days records for that user (using the created by column) for it to add up separately each of the types of passengers (Adults_Payers, Adults_Other,Children_Payers, Children_Other) so they can see what their totals are for the day (or at any point during the day what their total is)

 

My current formula just doesn't work, the date function doesn't work at all and I have no idea why as in my opinion it should. 

Sum(

Filter(Boat_Passenger_Numbers,

'Created By'.DisplayName=User().FullName,'Date-Day'=DatePicker.SelectedDate),

Adults_Payers)

This edited formula sort of works but for some reason just stops working after the 14th July, no reason why the data hasn't changed and there is only a small amount of test data (50 rows). Even though I have rounded the system's 'Created' date so it doesn't have any decimal places for time, if I put on equal sign it returns zero results.

Sum(

Filter(
Boat_Passenger_Numbers,

'Created By'.DisplayName="Christian Thomas",

Rounddown(Created,0)>DatePicker.SelectedDate,

Rounddown(Created,0)<DatePicker.SelectedDate+1),

Children_Payers)

 

I believe the delegation warnings don't matter as the formula will only be looking at the most recent data which per day the number of records added could be up to around 250.

PA-3.png

 

Any help you can give is greatly appreciated as although I pretty new to PowerApps I'm well versed with excel, the logic in PowerApps formula is something else!

 

Also please do not assume I know where you get items from or where they should be as, a lot of the posts I've found on the community reference things which I can never find within PowerApps or SharePoint list to be able to copy and reconfigure for my purpose, hence why I have given so much detail about the setup, so I'm hoping you can help within the format I need to implement.

 

Many thanks

2 REPLIES 2
StalinPonnusamy
Community Champion
Community Champion

 

 

 

With(
    {sData: 'Issue tracker'},
    Sum(
        Filter(
            sData,
            (RoundDown(
                'Date reported',
                0
            ) = DatePicker2.SelectedDate) && 'Created By'.DisplayName = User().FullName
        ),
        'Days old'
    )
)

 

 

 

StalinPonnusamy_2-1627217409239.png

 

SP

StalinPonnusamy_3-1627217548936.png

 

 

 

StalinPonnusamy
Community Champion
Community Champion

Hi @ChristianStAuby 

 

Please let us know if still any issues.

 

If you like this post, give it a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users to find it.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,174)