SharePoint List - group by, count and average

[zwornik80]

I have LIST 1 on which I need to do some summary statistic and put this in LIST 2, like shown below (only small sample to illustrate):

Column COUNTRIES_ON_TIME in LIST 2 comes from counting values = 0 in column DELIVERY_DELAY_DAYS in LIST 1
Column COUNTRIES_DELAYED in LIST 2 comes from counting values <> 0 in column DELIVERY_DELAY_DAYS in LIST 1
Column AVERAGE_DELAY_DAYS in LIST 2 comes from taking average from values <> 0 in column DELIVERY_DELAY_DAYS in LIST 1

What do I need to use (Flow, Fromulas?) to achieve what is in LIST 2?

[efialttes]

Hi!
So you need to consolidate continents from list 1 into list 2, right?
You can divide the task in two:
Subtask#1: build an array whose elements are the continents; and initialize variables for the counters you need. The array I am referring is:
["EUR","LATAM",...]

Subtask#2: assign the array as 'Apply to Each' input, now inside filter the items from List1 so you get only the ones related to current iteration, next increment counters, then create new item in List 2 and finally reset counters

Hooe this helps

[v-litu-msft · Accepted solution]

Hi @zwornik80,

You could initialize some variable to count the counties numbers and average delay time, then use the Apply to each + condition to increase it determined by their Rigon and Delay time, for example:

Then, use these math functions to process the average days, then append these data into the "List 2" table.

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

[zwornik80]

@v-litu-msft Thanks for suggestion. This is very usefull. Where in this flow I should refer to List 1 and List 2 to achieve what I need. Should I start with "Get Items" for List 1 and List 2, then fallow your steps with "Initialize variable" and "Apply to each"? How can use initialized variables to populate List 2?

[v-litu-msft]

Hi @zwornik80,

Initialize these variables before Apply to each action.

And you could use Add a row into a table to populate list 2.

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

[zwornik80]

@v-litu-msft  You've mentioned to use Math Functions to calculate Average. I don't see proper function for calculating Average in Math section. "Compose" do not provide such option. I've tried by initializing variable for Average and wanted to update using "Set variable" with formula =AVERAGE([DELIVERY_DELAY_DAYS]) but that is not accepted. Can you suggest how to do this?