Forum Discussion
Average age Sharepoint
Hello all kinda new to Sharepoint and had some issues finding any information if it is possible to get the average age calculated from an age column (as shown). I can't find anything about doing that specifically only about making the age column itself.
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)
15 Replies
wschaefer your age column (a calculated column) can be set to show just the number of working days with the following formula.As before, my date columns are called Start and End:
=IF(ISERROR(DATEDIF(Start,End,"d")),"",(DATEDIF(Start,End,"d"))+1-INT(DATEDIF(Start,End,"d")/7)*2-IF((WEEKDAY(End)-WEEKDAY(Start))<0,2,0)-IF(OR(AND(WEEKDAY(End)=7,WEEKDAY(Start)=7),AND(WEEKDAY(End)=1,WEEKDAY(Start)=1)),1,0)-IF(AND(WEEKDAY(Start)=1,(WEEKDAY(End)-WEEKDAY(Start))>0),1,0)-IF(AND(NOT(WEEKDAY(Start)=7),WEEKDAY(End)=7),1,0))
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)When i follow the steps as provides i keep running into a few issues one was i changed the names of my columns so i could fallow along easier (figured out why new names weren't working already)
Now it gives me a different error of cant use the column i selected it to grab the data from
wschaefer a filter query can't reference a calculated column. My example was not using a calculated column in the filter query.
Thank you for your response and yes that was a correct assumption that it's a calculated column I forgot to mention that, I will be trying this today if I have the time but I think this is exactly what I was looking for!! I also dislike the new designer I just started get comfortable with the old one.
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)wschaefer I assume your age column is a calculated column. If that's correct there is no way in SharePoint itself for it to iterate over all the items in the calculated column and produce an overall average. But this CAN be done with a flow in Power Automate. I will post up the solution as soon as I've done some screenshots, and I'll also show you how you can format the Age column to show the number of working days (i.e excluding weekends) as a whole number.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP (and classic 1967 Morris Traveller driver)Hello wschaefer
overall on all items? Click on the column and you have more options for totals, like Average:
Best, Dave
