cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering Sharepoint List and adding Sum

Greetings,

 

I am excited to work with powerapps. This is my first post and first power app. I am currently creating a Leave manager app that will submit requests and have a managers approval for that request. Things are going well.

 

However I want to add a Summary page that shows how many days have been taken and of what type. I can get a sum of all days just fine, however when I want to add a filter of what type of leave it is, I am falling short.

 

I currently am running a Sharepoint list. There are two variablest that I am workiing with: Status, is a choice list that states wether a request is: Pending, Approved or Declined. Then there is Number_Of_Days which is a simple number field.

 

Here is what I have so far.

 

"Total Days Taken: " & Sum('Leave Manager'.Number_Of_Days, 'Leave Manager'.Status="Approved")

 

If I do the first part of just using Sum to add the number of days it works well. I am getting an error that states "Invalid Arguement Type". It shows this right on the = sign. 

 

Would anyone have any ideas on how to improve this? I would also like to create fields that show each Type of Leave and how many days have been taken for that leave. 

 

Thank you.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Level 10

Re: Filtering Sharepoint List and adding Sum

The correct syntax is either this:

"Days of Sabbatical Leave: " & Sum(Filter('Leave Manager',Status.Value="Approved" && Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),Number_Of_Days)

Or this:

"Days of Sabbatical Leave: " & Sum(Filter('Leave Manager',Status.Value="Approved", Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),Number_Of_Days)

View solution in original post

9 REPLIES 9
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filtering Sharepoint List and adding Sum

Thanks for trying out PowerApps! One of the things that you can do with its formulas is to compose different functions, and this works well especially with tables / data sources (such as SharePoint lists). In this case you can combine the Sum and Filter functions to achieve what you want:

"TotalDaysTaken: " & Sum(Filter('Leave Manager', Status = "Approved"), Number_Of_Days)

It first filters the list based on the status, and then applies the sum to the result of that filter operation.

SKimble119
Level: Powered On

Re: Filtering Sharepoint List and adding Sum

I believe you have the wrong nesting order. Try the following:

Sum(Filter('Leave Manager',Status="Approved"),Number_Of_Days)

Anonymous
Not applicable

Re: Filtering Sharepoint List and adding Sum

Thank you for the replies.

 

I have tried the new nesting orders and it still says it has an "Invalid Argument Type". But there is no longer a yellow triangle with an exclamation point. It does have a blue dot instead.

 

Is there away to have it compare Text values?

 

 

Anonymous
Not applicable

Re: Filtering Sharepoint List and adding Sum

I was able to fix that.

 

I had to have Status.Value="Approved"

 

Here is the new line.

 

"Days Taken Total: " & Sum(Filter('Leave Manager' , Status.Value="Approved"), Number_Of_Days)

 

That does bring me to my other question. How can I add another Filter for Type of Leave.

 

Could I nest it again?

Highlighted
SKimble119
Level: Powered On

Re: Filtering Sharepoint List and adding Sum

I assume type of leave is just another field in the sharepoint list? If so, yes, you can nest it with and and() statement or use &&.

Anonymous
Not applicable

Re: Filtering Sharepoint List and adding Sum

I see to try the &&.

 

Here is my current statement now.

 

"Days of Sabbatical Leave: " & Sum(Filter('Leave Manager',Status.Value="Approved")&&Filter('Leave Manager',Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),Number_Of_Days)

 

It is saying that I have "Invalid Arguments". Can I not use && right away after the first filter?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Filtering Sharepoint List and adding Sum

You can use the && operator inside the Filter function:

"Days of Sabbatical Leave: " &
    Sum(
        Filter(
            'Leave Manager',Status.Value="Approved" && Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),
        Number_Of_Days)

You can also pass the next condition as an additional parameter to the Filter function: if you have multiple conditions, only items that satisty all conditions are returned:

"Days of Sabbatical Leave: " &
    Sum(
        Filter(
            'Leave Manager',Status.Value="Approved",
            Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),
        Number_Of_Days)
Meneghino
Level 10

Re: Filtering Sharepoint List and adding Sum

The correct syntax is either this:

"Days of Sabbatical Leave: " & Sum(Filter('Leave Manager',Status.Value="Approved" && Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),Number_Of_Days)

Or this:

"Days of Sabbatical Leave: " & Sum(Filter('Leave Manager',Status.Value="Approved", Type_x0020_of_x0020_Leave.Value="Sabbatical Leave"),Number_Of_Days)

View solution in original post

Anonymous
Not applicable

Re: Filtering Sharepoint List and adding Sum

Thank you for your replies. This has solved my issues. I am glad to see the nesting. 

 

This is a great community!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 345 members 5,809 guests
Please welcome our newest community members: