Forum Discussion

Jim_Allen's avatar
Jim_Allen
Copper Contributor
Jun 19, 2024
Solved

Sum of calculated Currency column without Power Automate

I have a Microsoft List for our projects with a calculated currency column calculating estimated fee income based on a percentage (from a separate column) of the project value column.

 

I want to have the Estimated fees column display a sum at the bottom, just like a numeric column can.

 

I understand that this can't currently be done with the currency column, but there are apparently workarounds with Power Automate.

 

I don't want to disappear down this rabbit hole/time vacuum at least for now, so I'm wondering if there is a simple workaround using a numeric column, with JSON custom formatting to display currency symbols and comma separation to turn number like this 11000 into this £11,000.00.

 

Is this too simplistic?

microsoft lists
  • Rob_Elliott's avatar
    Rob_Elliott
    Jun 20, 2024
    You can't sum a calculated column. You'd need a flow in Power Automate and have it update just the first item in the Sum column with the result.

4 Replies

Sort By
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Jun 19, 2024

    Jim_Allen with a number column you can display the total of an item and have a currency symbol using the following JSON:

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "font-weight": "bold",
    "background-color": "#fefbea"
  },
  "children": [
    {
      "elmType": "span",
      "txtContent": "£",
      "style": {
        "display": "inline-block",
        "padding-left": "6px"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

     

     

    But you can't use JSON to add the currency symbol to to the sum at the bottom of the column.

     

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

    • Jim_Allen's avatar
      Jim_Allen
      Copper Contributor
      Jun 19, 2024

      Rob_Elliott 

       

      Thanks Rob - it's a calculated column though with this formula: =[Budget]*[Fee percentage]

       

      I can't see how to to use number format columns with calculated values and the column totals.

       

      I can have calculated columns with number format and totals, calculated columns with currency, and number format columns with currency and totals, but apparently not calculated columns with totals.

       

      I did Google this, but answers seemed to be all dating back a couple of years. I was hoping there was a fix with more recent updates.

       

       

       

       

       

      • Rob_Elliott's avatar
        Rob_Elliott
        Bronze Contributor
        Jun 20, 2024
        You can't sum a calculated column. You'd need a flow in Power Automate and have it update just the first item in the Sum column with the result.

Resources