Groupby Using multiple Conditional Sum Fields

[cpolito]

I need assistance with creating a gallery.

I have a table that consists of the fields: 

crew_id, dayofweek2,hours

I want the gallery to group by crew_id and sum the hours based on the day the week.

So, every row should contain the fields: crew_id, mondaysum, Tuesdaysum, wendnesdaysum  .... totalsum

I This is my code so far but it's not grouping the sums:

AddColumns(

GroupBy(

HJ_FormanTotals,

"crew_id",

"Crew_id"

) ,

"MondaySum",

Sum(

Filter(

HJ_FormanTotals,

dayofweek2 = 2

),

hours

),

"WednesdaySum",

Sum(

Filter(

HJ_FormanTotals,

dayofweek2 = 3

),

hours

)

)

any help would be greatly appreciated. 

[rebeccas]

Try this (HJ_FormanTotals is a SharePoint List name?)

Screens OnVisible=

ClearCollect(CollTotals,
AddColumns(GroupBy(HJ_FormanTotals, "dayofWeek2", "GrpByDay"),
"SumofHrs", Sum(GrpByDay, hours)))

Gallery Items = CollTotals (or on your chart or wherever you are showing the values)

[cpolito]

HJ_FormanTotals is a collection that is collected upon entering the screen.  I cant create sums in a different table because this gallery will be used as a drill-down and I will need to see the numbers change dynamically as hours are altered.  This code would be very simple in SQL using  Sum(Case When ...  but is quite tricky so far in powerapps.   

[rebeccas · Accepted solution]

Wanting to drill down doesn't matter because you have a field to refer to that you can do a LookUp if you want to do that. This will still work for what you need.

There is a second option of where you do a Group By that does not include the sum and then get the sum in the gallery with a LookUp. I can't type that in without testing it to get the order correct. I'll post it in a few when I can test it and make sure I have in proper order.

[cpolito]

That did it. 

i just added this to the columns 

Sum(Filter(HJ_FormanTotals,crew_id = ThisItem.crew_id && dayofweek2 = 3),hours)

Will this make my gallery laggy? 

Thanks, for your help?

[rebeccas]

I found one I already had doing it quicker than expected. You can also do this:

Gallery Items=

GroupBy(HJ_ForemanTotals, "dayofWeek2", "GrpByDay")

In Gallery:

lbl_Day = ThisItem.dayofWeek2

lbl_Sum = Sum(Filter(HJ_ForemanTotals, dayofWeek2=ThisItem.dayofWeek2),hours)

This will sum it in the gallery for you. There are several ways you can achieve the same end result. 

As for it changing as needed it will...if the screen is left open you can have a timer that does a refresh and will update the data but otherwise it will when you open the screen on its on. 

And for the drill down....You can have a second gallery (either in this on as a sub gallery or outside it as a second on) and it will be filtering to show the related items. 

Click on Gallery one and have Set(varSelected, ThisItem.dayofWeek2)

Items for Gallery 2 = Filter(HJ_ForemanTotals, dayofWeek2=varSelected)

Something like this above will allow you to see more detail on each item regardless of how you do your grouping.

[rebeccas]

Awesome! 

It just depends on how much data you have. If you end up with a lot of data and you notice it going slow you can swap it over to a collection instead and that will help. I have a few that have a lot of data (esp ones that I have looking to SQL instead of SharePoint) so what I do is on the screens visible I put a screen that shows a loading Icon and message I use, then gets the data in the collection and then hides the loading icon...just so it is obvious to the user to wait a second. 

In regards to how current the data is...as long as you create the collection on the screens OnVisible it will be just as accurate as it is to look directly to the data source but will load faster. 

[alrez]

Hi,

It looks like you got a lot of help here and you were able to get your question answered. I just wanted to add in a bit more information for learning. Feel free to take a look.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

Regards,

Alex

-------

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