Filtering Sharepoint List and adding Sum

[Anonymous]

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.

[CarlosFigueira]

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]

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

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

[Anonymous]

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]

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]

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]

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?

[CarlosFigueira]

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 · Accepted solution]

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]

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

This is a great community!