Average age Sharepoint

wschaefer this post is quite long, so grab a glass of wine and strap yourself in for the ride. A new number column has been added to the list and we'll use it to populate item ID 1 with the average of ALL items as shown in the first image below:

1. In Power Automate I still prefer using the classic designer (as many of us do), but this will use the new designer. The trigger for the flow is a recurrence schedule set to run at 2am on Monday to Friday although you could run it at whatever schedule you want:

2. The first action in the flow is initialize a variable called varItems and we'll set the type to Float and with no initial value.This variable will hold the number of items in the list.

3. Next, add another initialize variable call varWorkingDays and it's also a Float variable.

4. We're adding the overall average into the list item that has an ID of 1, so add a SharePoint get item action, select the site and list and in the ID field enter 1:

5. We now want to get all the items from the list where the End column is not null (your column names will be different of course). So the Filter Query needs to be set to End ne null (where ne is not equal to😞

6. Next, add an apply to each and select value from the dynamic content box:

7. Inside the apply to each and an increment variable action, select the variable varItems and the value to 1

8. Next, add a Compose action and select WorkDays from the get items section of the dynamic content box:

9. Add another compose action and select the variable varWorkingDays from the dynamic content box:

10: Next, add a Set variable action and add the following expression (from the Expression tab of the dynamic content box)
add(outputs('ComposeWorkingDays'),float(outputs('ComposeWorkDays')))

11. Outside the apply to each add another compose and select the variable varWorkingDays:

12. Add another compose action and select the variable varItems:

13. Add yet another compose action (they really are my favourite!) and add the following expression to divide the working days variable by the items variable:

div(variables('varWorkingDays'), variables('varItems'))

14. By default the float variable will have 13 decimal places wich is a bit much, s we want to change it to 2 decimal places. This can be done with the Format number action:

15. Finally we add a SharePoint Update item action, select ID 1 and the title (from the get item section of the dynamic content box) and for the Average column select formatted number from the dynamic content box.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)