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
Highlighted
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)
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?

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)
Highlighted
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)
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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

Follow PowerApps on Twitter

Stay Up-to-Date by following PowerApps on Twitter

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 40 members 4,068 guests
Please welcome our newest community members: