cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tward
Level: Powered On

Sum when filtering sharepoint list with 2 items

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.

PA Sample 1.jpg

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:

PA Sample 2.jpg

Team & Task = Single Text Input

Reporting Date = Date & Time

Volume = Number

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sum when filtering sharepoint list with 2 items

Hi @tward,

 

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:10.JPG

 

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:11.JPG

 

12.JPG

 

 

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Sum when filtering sharepoint list with 2 items

Hi @tward,

 

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:10.JPG

 

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:11.JPG

 

12.JPG

 

 

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

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

tward
Level: Powered On

Re: Sum when filtering sharepoint list with 2 items

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 

Highlighted
tward
Level: Powered On

Re: Sum when filtering sharepoint list with 2 items

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)

pa sample 3.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,557)