List Customizer or Column Formatting to Sum a calculated column?

%3CLINGO-SUB%20id%3D%22lingo-sub-1272346%22%20slang%3D%22en-US%22%3EList%20Customizer%20or%20Column%20Formatting%20to%20Sum%20a%20calculated%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1272346%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20in%20modern%20experience%2C%20SharePoint%20Online%2C%20that%20I%20want%20to%20be%20able%20to%20sum%20a%20calculated%20column%2C%20like%20we%20are%20able%20to%20do%20with%20other%20columns.%20From%20what%20I%20have%20been%20reading%2C%20the%20traditional%20OOTB%20approach%20isn't%20achievable%2C%20so%20I'm%20wondering%20if%20it%20is%20possible%20to%20get%20this%20by%20utilizing%20list%20customizers%20or%20some%20fancy%20column%20formatting%20solution%3F%20Currently%2C%20with%20my%20limited%20knowledge%2C%20I%20can't%20find%20a%20solution%2C%20but%20am%20hoping%20the%20vast%20knowledge%20of%20the%20SharePoint%20world%20may%20be%20able%20to%20help%20with%20a%20solution%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EJennie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1272346%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1275162%22%20slang%3D%22en-US%22%3ERe%3A%20List%20Customizer%20or%20Column%20Formatting%20to%20Sum%20a%20calculated%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1275162%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jennie%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F98205%22%20target%3D%22_blank%22%3E%40Jennie%20Sheridan%3C%2FA%3E)%2C%20that%20was%20an%20interesting%20%26amp%3B%26nbsp%3B%20fun%20challenge%20and%20I%20had%20to%20think%20a%20bit%20out%20of%20the%20box%20to%20achieve%20it.%20But%20there%20is%20a%20way%20to%20do%20this%20using%20a%20flow%20in%20Power%20Automate%20and%20some%20JSON%20column%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20a%20list%20I%20already%20had%20which%20has%20a%20couple%20of%20number%20columns%20called%20Number%20and%20Times.%20There%20is%20then%20a%20calculated%20column%20called%20Total.%20I've%20added%202%20extra%20columns%3A%20a%20number%20column%20called%20FlowTotal%20where%20I've%20added%20a%20sum%20total%2C%20and%20a%20single%20line%20of%20text%20column%20called%20RunFlow%20for%20the%20button%20to%20trigger%20the%20flow.%20Whenever%20you%20want%20to%20update%20the%20sum%20of%20the%20FlowTotal%20column%20you%20need%20to%20press%20the%20button.%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%220-SPList.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181599iB166043CAD2F951A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%220-SPList.png%22%20alt%3D%220-SPList.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20RunFlow%20column%20is%20formatted%20with%20JSON%20code%20to%20only%20display%20the%20button%20to%20run%20the%20flow%20if%20the%20ID%20number%20of%20the%20item%20is%2045.%20If%20I%20hadn't%20done%20that%20the%20button%20would%20have%20displayed%20on%20every%20item%20in%20the%20list%20and%20we%20only%20need%20it%20in%20one%20place.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20JSON%20formatting%20code%20is%20shown%20below.%20You'll%20need%20to%20change%20the%20ID%20to%20be%20the%20ID%20of%20your%20flow%20(from%20the%20browser%20address%20bar%20when%20you%20are%20in%20Power%20Automate)%20and%20change%20the%20ID%20to%20whatever%20the%20ID%20is%20of%20the%20item%20where%20you%20want%20the%20button%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%0A%20%20%22elmType%22%3A%20%22button%22%2C%0A%20%20%22txtContent%22%3A%20%22Calculate%22%2C%0A%20%20%22customRowAction%22%3A%20%7B%0A%20%20%20%20%22action%22%3A%20%22executeFlow%22%2C%0A%20%20%20%20%22actionParams%22%3A%20%22%7B%5C%22id%5C%22%3A%20%5C%2289bca802-d246-456f-b58f-e6c326eb9cf2%5C%22%7D%22%0A%20%20%7D%2C%0A%20%20%22style%22%3A%20%7B%0A%20%20%20%20%22background-color%22%3A%20%22%2302767a%22%2C%0A%20%20%20%20%22color%22%3A%20%22%23ffffff%22%2C%0A%20%20%20%20%22outline%22%3A%20%22transparent%22%2C%0A%20%20%20%20%22border-width%22%3A%20%221px%22%2C%0A%20%20%20%20%22border-style%22%3A%20%22solid%22%2C%0A%20%20%20%20%22border-color%22%3A%20%22transparent%22%2C%0A%20%20%20%20%22cursor%22%3A%20%22pointer%22%2C%0A%20%20%20%20%22font-size%22%3A%20%2212px%22%2C%0A%20%20%20%20%22visibility%22%3A%20%22%3Dif((%5B%24ID%5D%20%3D%3D%20'45')%2C'visible'%2C'hidden')%22%0A%20%20%7D%0A%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20flow%20the%20trigger%20is%20a%20SharePoint%20%3CEM%3EFor%20a%20selected%20item%3C%2FEM%3E%20trigger%20and%20in%20it%20you%20select%20your%20site%20and%20list.%20The%20first%20action%20is%20a%20SharePoint%20%3CEM%3EGet%20items%3C%2FEM%3E%20action%20as%20we%20want%20to%20get%20all%20the%20items%20in%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20add%20an%20%3CEM%3EApply%20to%20each%3C%2FEM%3E%20and%20select%20%3CSTRONG%3Evalue%3C%2FSTRONG%3E%20from%20the%20dynamic%20content.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20add%20a%20%3CEM%3ECompose%3C%2FEM%3E%20action%20and%20select%20the%20%3CSTRONG%3ETotal%3C%2FSTRONG%3E%20column%20from%20the%20dynamic%20content.%20I've%20renamed%20this%20to%20ComposeTotal%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221-Flow.png%22%20style%3D%22width%3A%20645px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181600i4ADBA8EEAAF77EF8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221-Flow.png%22%20alt%3D%221-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20add%20another%20%3CEM%3ECompose%3C%2FEM%3E%20action%20(I've%20renamed%20it%20to%20ComposeInt).%20Click%20in%20the%20field%20and%20when%20the%20dynamic%20content%20opens%20click%20the%20Expression%20tab%20and%20type%26nbsp%3B%3CSTRONG%3Eint(outputs('ComposeTotal'))%3C%2FSTRONG%3E%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Efollowed%20by%20OK.%20A%20compose%20control%20outputs%20in%20text%20format%20so%20we%20need%20to%20change%20the%20total%20back%20to%20an%20integer%2C%20hence%20the%20int%20in%20the%20expression.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ENext%20add%20a%20%3CEM%3EFormat%20number%3C%2FEM%3E%20action%20and%20select%20the%20ComposeInt%20output%20from%20the%20dynamic%20content.%20You%20can%20leave%20the%20format%20as%20the%20default%20with%202%20decimal%20places%20or%2C%20as%20I%20have%20done%2C%20just%20enter%20a%20%23%20which%20will%20remove%20the%20decimal%20places.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%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-Flow.png%22%20style%3D%22width%3A%20598px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181597iEA70B106A49BA68D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222-Flow.png%22%20alt%3D%222-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFinally%20we%20update%20the%20SharePoint%20item%2C%20selecting%20for%20the%20FlowTotal%20column%20the%20output%20of%20the%20Format%20number%20action.%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-Flow.png%22%20style%3D%22width%3A%20614px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181606iAC3B0041DA5F891B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%223-Flow.png%22%20alt%3D%223-Flow.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20very%20large%20list%20it%20will%20take%20a%20while%20for%20the%20flow%20to%20run%20and%20to%20populate%20the%20FlowTotal%20column%20and%20display%20the%20sum.%20And%20even%20on%20a%20small%20list%20like%20this%20it%20takes%20a%20few%20seconds%2C%26nbsp%3B%20but%20it%20works%20successfully.%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-SP-Result.png%22%20style%3D%22width%3A%20951px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181601i41CA83907FAFD218%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%224-SP-Result.png%22%20alt%3D%224-SP-Result.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20a%20very%20short%20video%20below%20which%20shows%20it%20in%20action.%20Hope%20that%20helps%20but%20come%20back%20with%20any%20questions%20about%20it.%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%22sumCalculatedColumn.gif%22%20style%3D%22width%3A%20800px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181610iFA267FC06FD176BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22sumCalculatedColumn.gif%22%20alt%3D%22sumCalculatedColumn.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20variation%20of%20the%20flow%20would%20be%20to%20run%20it%20whenever%20any%20item%20in%20the%20list%20was%20created%20or%20modified.%20That%20would%20remove%20the%20need%20for%20the%20button.%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23333399%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a list in modern experience, SharePoint Online, that I want to be able to sum a calculated column, like we are able to do with other columns. From what I have been reading, the traditional OOTB approach isn't achievable, so I'm wondering if it is possible to get this by utilizing list customizers or some fancy column formatting solution? Currently, with my limited knowledge, I can't find a solution, but am hoping the vast knowledge of the SharePoint world may be able to help with a solution?

 

Any help appreciated!

 

Thanks,

Jennie

3 Replies
Highlighted

Hi Jennie (@Jennie Sheridan), that was an interesting &  fun challenge and I had to think a bit out of the box to achieve it. But there is a way to do this using a flow in Power Automate and some JSON column formatting.

 

I've used a list I already had which has a couple of number columns called Number and Times. There is then a calculated column called Total. I've added 2 extra columns: a number column called FlowTotal where I've added a sum total, and a single line of text column called RunFlow for the button to trigger the flow. Whenever you want to update the sum of the FlowTotal column you need to press the button.

 

0-SPList.png

 

The RunFlow column is formatted with JSON code to only display the button to run the flow if the ID number of the item is 45. If I hadn't done that the button would have displayed on every item in the list and we only need it in one place.

 

The JSON formatting code is shown below. You'll need to change the ID to be the ID of your flow (from the browser address bar when you are in Power Automate) and change the ID to whatever the ID is of the item where you want the button:

 

 

{
  "elmType": "button",
  "txtContent": "Calculate",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"89bca802-d246-456f-b58f-e6c326eb9cf2\"}"
  },
  "style": {
    "background-color": "#02767a",
    "color": "#ffffff",
    "outline": "transparent",
    "border-width": "1px",
    "border-style": "solid",
    "border-color": "transparent",
    "cursor": "pointer",
    "font-size": "12px",
    "visibility": "=if(([$ID] == '45'),'visible','hidden')"
  }
}

 

 

 

In the flow the trigger is a SharePoint For a selected item trigger and in it you select your site and list. The first action is a SharePoint Get items action as we want to get all the items in the list.

 

Next, add an Apply to each and select value from the dynamic content. 

 

Then add a Compose action and select the Total column from the dynamic content. I've renamed this to ComposeTotal:

1-Flow.png

 

Next, add another Compose action (I've renamed it to ComposeInt). Click in the field and when the dynamic content opens click the Expression tab and type int(outputs('ComposeTotal'))

followed by OK. A compose control outputs in text format so we need to change the total back to an integer, hence the int in the expression.
 
Next add a Format number action and select the ComposeInt output from the dynamic content. You can leave the format as the default with 2 decimal places or, as I have done, just enter a # which will remove the decimal places.

 

2-Flow.png

 

Finally we update the SharePoint item, selecting for the FlowTotal column the output of the Format number action.

 

3-Flow.png

 

If you have a very large list it will take a while for the flow to run and to populate the FlowTotal column and display the sum. And even on a small list like this it takes a few seconds,  but it works successfully.

 

4-SP-Result.png

 

There's a very short video below which shows it in action. Hope that helps but come back with any questions about it.

 

sumCalculatedColumn.gif

 

A variation of the flow would be to run it whenever any item in the list was created or modified. That would remove the need for the button.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

 

Highlighted

Wow, thank you @Rob Elliott!  I work with Jennie and am going to put this to use.  Very much appreciated!

Highlighted

@Jeff MacPhail glad I could help.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User