Sum when filtering sharepoint list with 2 items

[Anonymous]

Hi all,

Fairly new to Powerapps and tried to search on previous posts but couldn't quite get what i needed.

I have a tool that will be used for workload data capture that is sent to sharepoint when added.

I am trying to create a screen that will display volumes of tasks that were completed on the selected date and also filtered to which team these tasks were completed by.

I created a gallery for this and have managed to get the task list to populate based on the following formula:

Distinct(Filter('Source', Dropdown1.Selected.Result = Team, DatePicker3.SelectedDate <= Reporting_x0020_Date), Task)

I then add a label to the right hand side, changing the end of the formula to "Volume" with no luck. I have also tried several versions of Sums & Filter formula aswell.

As there will be multiple users inputting task volumes i would expect multiple returns for each task so in the sample below, there are 2x entries for 2000000111s input for Team 1 on 27/08/2018 with volumes of 10 & 10 so would expect a return of 20.

The highlighted warning displayed relates to Delegation - i would only ever be needing the previous few hundred entries so this shouldn't affect the search.

This is a sample of the sharepoint list:

Team & Task = Single Text Input

Reporting Date = Date & Time

Volume = Number

Thanks

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Do you want to sum the Volume column value for each task within the Gallery of your app?

Could you please share a bit more about the formula that you use to sum the Volume column value within your app?

I have created a SP list on my side, the data structure as below:

If you want to sum the Volume column value for each task within the Gallery of your app, I think the GroupBy function could achieve your needs (instead of Distinct function). I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery (Gallery1) control to following formula:

GroupBy(
    Filter('20180904_case6',Team=Dropdown1.Selected.Value,DatePicker1.SelectedDate<=Reporting_x0020_Date),
    "Task",
    "GroupData"
)

Note: The '20180904_case6' represents the SP list data source within my app.

On your side, you should type the following formula:

GroupBy(
    Filter('Source', Dropdown1.Selected.Result = Team, DatePicker3.SelectedDate <= Reporting_x0020_Date),
    "Task",
    "GroupData"
)

Within the Gallery control, add two Label controls. The Text property of the Left Label control set to following formula:

ThisItem.Task

The Text property of the right Label control set to following formula:

Sum(ThisItem.GroupData,Volume)  /* <-- Sum the Volume column value for each task  */

More details about the GroupBy function in PowerApps, please check the following article:

GroupBy function

Best regards,

Kris

[Anonymous]

Hi Kris,

Appreciate the help. The formula you provided helped me figure out how to filter between both, adding to this though - i needed to be specific about the date also.

To do this, i created a label that provided the text value of the datepicker (this was being formatted in US rather than UK as 9/3/18 for example) which caused me issues pulling the correct Sums.

New formula: 

GroupBy(Filter('Source',Title=Dropdown2.Selected.Value,Reporting_Date=Label12.Text),"Task","Reporting_Date","GroupData")

Many thanks 

[Anonymous]

Hi @v-xida-msft,

Just following on from this initial request, we are pulling correctly according to what was discussed.

There is a 3rd filter that we are looking to use: Workflow

This has 2 options, Completed & Received.

After we have our sum we would then look to filter this between 2 labels. I have attached a screenshot below of what we currently receive - which is the full amount(non filtered).

Any ideas how we go about doing this? I tried Filter the label but with no luck. Otherwise, i was thinking of a nested gallery

Formula currently: Sum(ThisItem.GroupData,Volume)

Thanks