Como calcular a média aritimética entre varios itens (registros) de uma mesma coluna no SharePoint?

%3CLINGO-SUB%20id%3D%22lingo-sub-1371636%22%20slang%3D%22en-US%22%3EComo%20calcular%20a%20m%C3%A9dia%20aritim%C3%A9tica%20entre%20varios%20itens%20(registros)%20de%20uma%20mesma%20coluna%20no%20SharePoint%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1371636%22%20slang%3D%22en-US%22%3E%3CP%3EComo%20eu%20posso%20obter%20o%20resultado%20do%20c%C3%A1lculo%20da%20m%C3%A9dia%20aritim%C3%A9tica%20entre%20varios%20itens%20(linha%20de%20registro)%20de%20uma%20lista%20para%20a%20mesma%20coluna%20no%20SharePoint%3F%3C%2FP%3E%3CP%3EExemplo%3A%3C%2FP%3E%3CP%3ELista%3A%20Exemplo%3C%2FP%3E%3CP%3ENome%20Col1%20(Valor)%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BCol2%20-%20(M%C3%A9dia%20Aritim%C3%A9tica%20dos%20valores%20da%20coluna%201)%3C%2FP%3E%3CP%3EJhon%26nbsp%3B%20%26nbsp%3B%2010%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B10%3C%2FP%3E%3CP%3EPeter%26nbsp%3B%20%26nbsp%3B20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B((10%20%2B%2020)%2F2)%20%3D%2015%3C%2FP%3E%3CP%3EMax%26nbsp%3B%20%26nbsp%3B%2020%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B(10%20%2B%2020%20%2B%2030)%2F3)%20%3D%2020%3C%2FP%3E%3CP%3E.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%3C%2FP%3E%3CP%3E.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%3C%2FP%3E%3CP%3E.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1371636%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1374160%22%20slang%3D%22en-US%22%3ERe%3A%20Como%20calcular%20a%20m%C3%A9dia%20aritim%C3%A9tica%20entre%20varios%20itens%20(registros)%20de%20uma%20mesma%20coluna%20no%20SharePoi%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1374160%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F659914%22%20target%3D%22_blank%22%3E%40evaldobaladelli%3C%2FA%3E%26nbsp%3BI%20don't%20know%20of%26nbsp%3B%20a%20way%20to%20do%20this%20in%20SharePoint%20as%20it%20can't%20loop%20through%20the%20items%20in%20the%20list.%20So%20you%20either%20need%20to%20do%20something%20in%20Power%20Apps%20or%20build%20a%20flow%20in%20Power%20Automate%20which%20is%20what%20we're%20going%20to%20do%20here%20to%20get%20the%20items%20in%20your%20SharePoint%20list%2C%20loop%20through%20each%20one%2C%20do%20a%20calculation%20and%20save%20the%20median%20value%20of%20each%20item%20back%20in%20the%20SharePoint%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20list%20in%20SharePoint%3A%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%220-SP-list.png%22%20style%3D%22width%3A%20477px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190052i064C891BCE19DDE7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SP-list.png%22%20alt%3D%220-SP-list.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Power%20Automate%20the%20flow%20starts%20with%20a%20%3CEM%3ERecurrence%20schedule%3C%2FEM%3E%20trigger.%20I've%20set%20it%20to%20run%20every%201%20day%20but%20you%20could%20do%20it%20as%20frequently%20as%20necessary.%20Or%20perhaps%20use%20a%20SharePoint%20%3CEM%3EWhen%20an%20item%20is%20created%3C%2FEM%3E%20trigger.%20Next%2C%20add%202%20%3CEM%3EInitialize%20variable%3C%2FEM%3E%20controls%20and%20make%20them%20both%20%3CSTRONG%3EFloat%3C%2FSTRONG%3E%20type.%201%20is%20to%20store%20the%20row%20count%20and%20one%20is%20to%20store%20the%20total%20score.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-trigger-initialize.png%22%20style%3D%22width%3A%20581px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190054iA264BA87C27B233B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221-trigger-initialize.png%22%20alt%3D%221-trigger-initialize.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20add%20a%20SharePoint%20%3CEM%3EGet%20items%3C%2FEM%3E%20action%2C%20select%20your%20site%20and%20list%20and%20I've%20set%20the%20order%20to%20ID%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222-Get.png%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190055iFAD25D31F72B089D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222-Get.png%22%20alt%3D%222-Get.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAdd%20an%20%3CEM%3Eapply%20to%20each%3C%2FEM%3E%20control%20and%20select%20%3CSTRONG%3Evalue%3C%2FSTRONG%3E%20from%20the%20dynamic%20content.%20Inside%20the%20apply%20to%20each%20add%202%20%3CEM%3EIncrement%20variable%3C%2FEM%3E%20controls%20the%20row%20count%20one%20to%201%20and%20the%20total%20variable%20to%20the%20score%2C%20selected%20from%20the%20dynamic%20content%20box%3A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%223-apply-increment.png%22%20style%3D%22width%3A%20592px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190053iE6C8EAA2A3736642%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%223-apply-increment.png%22%20alt%3D%223-apply-increment.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20add%20a%20Compose%20control%20and%20in%20this%20you%20will%20add%20an%20expression%3A%3CBR%20%2F%3E%3CSTRONG%3Ediv(variables('varTotal')%2Cvariables('varCount'))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewhich%20will%20divide%20the%20current%20total%20by%20the%20current%20row%20count%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%224-composeMedian.png%22%20style%3D%22width%3A%20955px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190056i568BE5D4B83140AD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%224-composeMedian.png%22%20alt%3D%224-composeMedian.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20final%20step%20is%20to%20update%20the%20SharePoint%20list%20item%20with%20the%20output%20of%20the%20Compose%20control.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%225-updateItem.png%22%20style%3D%22width%3A%20920px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190058i51E4078D96ABB16A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%225-updateItem.png%22%20alt%3D%225-updateItem.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20result%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%226-SP-Result.png%22%20style%3D%22width%3A%20475px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190057i3DC550043F4F5252%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%226-SP-Result.png%22%20alt%3D%226-SP-Result.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Como eu posso obter o resultado do cálculo da média aritimética entre varios itens (linha de registro) de uma lista para a mesma coluna no SharePoint?

Exemplo:

Lista: Exemplo

Nome Col1 (Valor)       Col2 - (Média Aritimética dos valores da coluna 1)

Jhon    10                     10

Peter   20                     ((10 + 20)/2) = 15

Max    20                     (10 + 20 + 30)/3) = 20

.          .                          .

.          .                          .

.          .                          .

 

 

1 Reply
Highlighted

@evaldobaladelli I don't know of  a way to do this in SharePoint as it can't loop through the items in the list. So you either need to do something in Power Apps or build a flow in Power Automate which is what we're going to do here to get the items in your SharePoint list, loop through each one, do a calculation and save the median value of each item back in the SharePoint list.

 

The list in SharePoint:


0-SP-list.png

 

In Power Automate the flow starts with a Recurrence schedule trigger. I've set it to run every 1 day but you could do it as frequently as necessary. Or perhaps use a SharePoint When an item is created trigger. Next, add 2 Initialize variable controls and make them both Float type. 1 is to store the row count and one is to store the total score.

 

1-trigger-initialize.png

 

Next add a SharePoint Get items action, select your site and list and I've set the order to ID:

 

2-Get.png

 

Add an apply to each control and select value from the dynamic content. Inside the apply to each add 2 Increment variable controls the row count one to 1 and the total variable to the score, selected from the dynamic content box:.

 

3-apply-increment.png

 

Next add a Compose control and in this you will add an expression:
div(variables('varTotal'),variables('varCount'))

which will divide the current total by the current row count:

 

4-composeMedian.png

 

The final step is to update the SharePoint list item with the output of the Compose control.

 

5-updateItem.png

 

This is the result:

 

6-SP-Result.png

 

Rob
Los Gallardos

Microsoft Power Automate Community Super User