# UI table percentage Calculation
**URL:** https://discourse.nodered.org/t/ui-table-percentage-calculation/50741
**Category:** Dashboard
**Created:** [8 September 2021 06:16 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741 "2021-09-08T06:16:02Z")
**Posts on this page:** 17
**Page:** 1
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [8 September 2021 06:16 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/1 "2021-09-08T06:16:02Z")
I have been experimenting with msg.ui\_control to construct a table of profit and loses. I have been able to do addition of individual columns but now I want to calculate net profit/loss which involves dividing the sum of one column with another, which i am not able to accomplish.
In this image I want to do the following maths operation and show it below the Total Profit% column.
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [8 September 2021 10:06 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/2 "2021-09-08T10:06:11Z")
Hi.
I think you have to write your own calculation function. As you have access to the complete table data it should be doable.
> **[Tabulator - Interactive JavaScript Tables](http://tabulator.info/docs/4.7/column-calcs#func-custom)**
>
> Create interactive data tables in seconds with Tabulator. A lightweight, fully featured JavaScript table generation library.
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [8 September 2021 11:01 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/3 "2021-09-08T11:01:09Z")
Yes I have seen that but I am not able to get it working. Any pointer towards that would be helpful
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [8 September 2021 16:32 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/4 "2021-09-08T16:32:59Z")
Perhaps you can post the relevant part of your flow to make it easier to give you the right starting point
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [9 September 2021 01:25 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/5 "2021-09-09T01:25:31Z")
I am experimenting with custom calculation functions, will update soon, In the meantime how can I change the background color of a cell in accordance to the value? I want to change the color of a cell to red if the numeric value is negative and green if positive.
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [9 September 2021 05:55 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/6 "2021-09-09T05:55:35Z")
Hi,
you can an example how to change the background (or any other css attribute) here: [Ui-table row change background color on updateorAddData - #4 by dceejay](https://discourse.nodered.org/t/ui-table-row-change-background-color-on-updateoradddata/29849/4)
Another good starting point how to work with ui\_control is the exampe 6:
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [9 September 2021 07:59 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/7 "2021-09-09T07:59:22Z")
How can I set the property of each column and sub column using msg.ui\_control?
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [9 September 2021 08:33 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/8 "2021-09-09T08:33:55Z")
Simple By sending ui\_control.tabulator messages. I can‘t see or experienced any limitations.
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [9 September 2021 09:11 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/9 "2021-09-09T09:11:00Z")
And these messages accumulate. So you can keep your basic configuration (in the config dialog) and add additional parameters to them. For the column array: When the property = field parameter matches it will be added to a specific column. See example 6
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [9 September 2021 10:25 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/10 "2021-09-09T10:25:25Z")
Basically my expertise is in mechanical engineering, thats why I am creating whole sort of such problems. Here in this case I am passing this ui\_control msg
```auto
{
"tabulator": {
"minHeight": 600,
"autoColumns": true,
"columnHeaderVertAlign": "middle",
"hozAlign": "center",
"columns": [
{
"title": "Fund Name",
"field": "Fund Name",
"formatter": "plaintext",
"align": "center",
"width": 250,
"headerVertical": true
},
{
"title": "SIP Info",
"formatter": "plaintext",
"hozAlign": "center",
"align": "center",
"columns": [
{
"title": "SIP Amount",
"field": "SIP Amount",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "SIP Date",
"field": "SIP Date",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Day Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Day Change",
"property": "dayreturns",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Day Change %",
"property": "dayreturns%",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Monthly Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Month Change",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Month Change %",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
},
{
"title": "Investment",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Total Value",
"field": "Total Value",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Invested",
"field": "Invested",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
}
]
},
{
"title": "Total Returns",
"formatter": "plaintext",
"align": "center",
"columns": [
{
"title": "Amount",
"field": "Total Profit",
"formatter": "plaintext",
"align": "center",
"headerVertical": true,
"bottomCalc": "sum"
},
{
"title": "Percentage",
"field": "Total Profit %",
"formatter": "plaintext",
"align": "center",
"headerVertical": true
}
]
}
]
},
"height": "600px"
}
```
And as in the example 6, I am using a button node to sent rowFormatter topic messages to this with the following payload.
```auto
function(row){ if(row.getData().dayreturns>0){ row.getElement().style.backgroundColor = "#32CD32"; } else { row.getElement().style.backgroundColor = "#DC143C"; } },
```
I am expecting the rows of table with dayreturn field less than 0 to be red or else green. But all the table changes to red.
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [11 September 2021 02:58 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/11 "2021-09-11T02:58:09Z")
If there are sub columns under a column, how will the property be defined?, I mean if I am not specifically mentioning the property payload in the msg.ui\_control
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [11 September 2021 07:30 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/12 "2021-09-11T07:30:52Z")
Sorry for the delay (I had a busy week)
You will need a callback function as described in the docs. Your function will look like this:
```auto
var totalPercentage = function(values, data, calcParams){
var sumOfTotalInvested = 0;
var sumOfTotalProfit = 0;
data.forEach((row) => {
sumOfTotalInvested += row["Total Invested"];
sumOfTotalProfit += row["Total Profit"];
});
console.log({ sumOfTotalInvested, sumOfTotalProfit});
return sumOfTotalInvested / sumOfTotalProfit / 100;
}
```
I use a unconnected function node to write these. Then I copy the function (without `var totalPercentage = ` into the correct parameter (in your case `buttomCalc`). I use the visual editor as eliminating new line and escaping inverted commas is done there automatically:
Simply `CTRL+A` and paste.
as you already define your complete table via one ui\_control message you can simply add it here. I haven't tested the accumulated updates for nested columns but in your case you don't need that.
Hope this helps (I simplified your definition ... I hope you get the point.)
a little tip: if possible avoid spaces and special caracters as field parameters. `"field":"totalProfitPercent"` is better as you can then write `row.totalProfitPercent`instead of `row["Total Profit %"];`
```auto
[{"id":"483a640b4504e770","type":"ui_table","z":"d2952ff54c89c139","group":"11346c8e6b90e622","name":"","order":0,"width":"10","height":"7","columns":[],"outputs":0,"cts":false,"x":470,"y":220,"wires":[]},{"id":"67f4ade2fbe62d03","type":"inject","z":"d2952ff54c89c139","name":"ui_control","props":[{"p":"ui_control","v":"{\"tabulator\":{\"columns\":[{\"title\":\"Fund Name\",\"field\":\"Fund Name\",\"formatter\":\"plaintext\",\"align\":\"center\",\"width\":250,\"headerVertical\":true},{\"title\":\"Total Returns\",\"formatter\":\"plaintext\",\"align\":\"center\",\"columns\":[{\"title\":\"Invested\",\"field\":\"Total Invested\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Profit\",\"field\":\"Total Profit\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Percentage\",\"field\":\"Total Profit %\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"function(values, data, calcParams){ var sumOfTotalInvested = 0; var sumOfTotalProfit = 0; data.forEach((row) => { sumOfTotalInvested += row[\\\"Total Invested\\\"]; sumOfTotalProfit += row[\\\"Total Profit\\\"]; }); console.log({ sumOfTotalInvested, sumOfTotalProfit}); return sumOfTotalInvested / sumOfTotalProfit / 100; }\"}]}]},\"customHeight\":12}","vt":"json"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payloadType":"str","x":280,"y":220,"wires":[["483a640b4504e770","2f98e4576c386d48"]]},{"id":"48f50f8d64bb5b9e","type":"inject","z":"d2952ff54c89c139","name":"Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Fund Name\":\"Line 2\",\"Total Invested\":50000,\"Total Profit\":5000,\"Total Profit %\":10},{\"Fund Name\":\"Line 3\",\"Total Invested\":50000,\"Total Profit\":1000,\"Total Profit %\":5},{\"Fund Name\":\"Line 4\",\"Total Invested\":50000,\"Total Profit\":2000,\"Total Profit %\":5},{\"Fund Name\":\"Line 5\",\"Total Invested\":50000,\"Total Profit\":6000,\"Total Profit %\":7},{\"Fund Name\":\"Line 6\",\"Total Invested\":50000,\"Total Profit\":11000,\"Total Profit %\":12}]","payloadType":"json","x":290,"y":260,"wires":[["483a640b4504e770"]]},{"id":"2f98e4576c386d48","type":"debug","z":"d2952ff54c89c139","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":260,"wires":[]},{"id":"1a7d2840b336fccd","type":"function","z":"d2952ff54c89c139","name":"callback functions","func":"var totalPercentage = function(values, data, calcParams){\n var sumOfTotalInvested = 0;\n var sumOfTotalProfit = 0;\n data.forEach((row) => {\n sumOfTotalInvested += row[\"Total Invested\"];\n sumOfTotalProfit += row[\"Total Profit\"];\n });\n \n console.log({ sumOfTotalInvested, sumOfTotalProfit});\n\n return sumOfTotalInvested / sumOfTotalProfit / 100;\n}\n\nvar testCalc = function(values, data, calcParams){\n debugger;\n return 123;\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":220,"wires":[[]]},{"id":"11346c8e6b90e622","type":"ui_group","name":"Calculation Functions","tab":"e33f9653df8ec25d","order":1,"disp":true,"width":"10","collapse":true},{"id":"e33f9653df8ec25d","type":"ui_tab","name":"Table","icon":"dashboard","disabled":false,"hidden":false}]
```
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [11 September 2021 09:13 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/13 "2021-09-11T09:13:52Z")
Thanks bro, Sorted Everything Out, I really appreciate your help.
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [11 September 2021 09:29 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/14 "2021-09-11T09:29:10Z")
I need help with one more small thing. i am currently using the following payload
```auto
function(row){ if(row.getData().DayChange>0){ row.getElement().style.backgroundColor = "#096B00"; } else { row.getElement().style.backgroundColor = "#DC143C"; } },
```
with the topic `rowFormatter` to colorize the entire rows red if the value of the element in column DayChange of that column is negative and green if positive. This is working fine but the problem is that there are other columns such as MonthlyChange and all.
I am thinking of a method to check each element in the columns that i want and colorize that cell, red if negative, green if positive. Hope you can help.
---
### Author:  [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)
#### Post date: [11 September 2021 12:35 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/15 "2021-09-11T12:35:09Z")
This works simmilar to a row formatter .. [here you need a cell formatter](http://tabulator.info/docs/4.7/format#format-custom):
```auto
var cellFormatter = function(cell, formatterParams, onRendered){
let value = cell.getValue();
if (value > formatterParams.threshold) {
cell.getElement().style.backgroundColor = formatterParams.aboveColor;
} else {
cell.getElement().style.backgroundColor = formatterParams.belowColor;
}
return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : '');
}
```
You can either use a individual callback for every column or use the `formatterParams` object to use the same code and tweak the parameters for every column. This way it is easier to edit your json
```auto
[{"id":"483a640b4504e770","type":"ui_table","z":"d2952ff54c89c139","group":"11346c8e6b90e622","name":"","order":0,"width":"10","height":"7","columns":[],"outputs":0,"cts":false,"x":470,"y":220,"wires":[]},{"id":"67f4ade2fbe62d03","type":"inject","z":"d2952ff54c89c139","name":"ui_control","props":[{"p":"ui_control","v":"{\"tabulator\":{\"columns\":[{\"title\":\"Fund Name\",\"field\":\"Fund Name\",\"formatter\":\"plaintext\",\"align\":\"center\",\"width\":250,\"headerVertical\":true},{\"title\":\"Total Returns\",\"formatter\":\"plaintext\",\"align\":\"center\",\"columns\":[{\"title\":\"Invested\",\"field\":\"Total Invested\",\"formatter\":\"plaintext\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Profit\",\"field\":\"Total Profit\",\"formatterParams\":{\"threshold\":2000,\"aboveColor\":\"#900000\",\"belowColor\":\"#009000\",\"unit\":\"$\"},\"formatter\":\"function(cell, formatterParams, onRendered){ let value = cell.getValue(); if (value > formatterParams.threshold) { cell.getElement().style.backgroundColor = formatterParams.aboveColor; } else { cell.getElement().style.backgroundColor = formatterParams.belowColor; } return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : ''); }\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"sum\"},{\"title\":\"Percentage\",\"field\":\"Total Profit %\",\"formatterParams\":{\"threshold\":10,\"aboveColor\":\"#DC143C\",\"belowColor\":\"#096B00\",\"unit\":\"%\"},\"formatter\":\"function(cell, formatterParams, onRendered){ let value = cell.getValue(); if (value > formatterParams.threshold) { cell.getElement().style.backgroundColor = formatterParams.aboveColor; } else { cell.getElement().style.backgroundColor = formatterParams.belowColor; } return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : ''); }\",\"align\":\"center\",\"headerVertical\":true,\"bottomCalc\":\"function(values, data, calcParams){ var sumOfTotalInvested = 0; var sumOfTotalProfit = 0; data.forEach((row) => { sumOfTotalInvested += row[\\\"Total Invested\\\"]; sumOfTotalProfit += row[\\\"Total Profit\\\"]; }); console.log({ sumOfTotalInvested, sumOfTotalProfit}); return sumOfTotalInvested / sumOfTotalProfit / 100; }\"}]}]},\"customHeight\":12}","vt":"json"},{"p":"payload"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payloadType":"str","x":280,"y":220,"wires":[["483a640b4504e770","2f98e4576c386d48"]]},{"id":"48f50f8d64bb5b9e","type":"inject","z":"d2952ff54c89c139","name":"Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Fund Name\":\"Line 2\",\"Total Invested\":50000,\"Total Profit\":5000,\"Total Profit %\":10},{\"Fund Name\":\"Line 3\",\"Total Invested\":50000,\"Total Profit\":1000,\"Total Profit %\":5},{\"Fund Name\":\"Line 4\",\"Total Invested\":50000,\"Total Profit\":2000,\"Total Profit %\":5},{\"Fund Name\":\"Line 5\",\"Total Invested\":50000,\"Total Profit\":6000,\"Total Profit %\":7},{\"Fund Name\":\"Line 6\",\"Total Invested\":50000,\"Total Profit\":11000,\"Total Profit %\":12}]","payloadType":"json","x":290,"y":260,"wires":[["483a640b4504e770"]]},{"id":"2f98e4576c386d48","type":"debug","z":"d2952ff54c89c139","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":470,"y":260,"wires":[]},{"id":"1a7d2840b336fccd","type":"function","z":"d2952ff54c89c139","name":"callback functions","func":"var totalPercentage = function(values, data, calcParams){\n var sumOfTotalInvested = 0;\n var sumOfTotalProfit = 0;\n data.forEach((row) => {\n sumOfTotalInvested += row[\"Total Invested\"];\n sumOfTotalProfit += row[\"Total Profit\"];\n });\n \n console.log({ sumOfTotalInvested, sumOfTotalProfit});\n\n return sumOfTotalInvested / sumOfTotalProfit / 100;\n}\n\nvar testCalc = function(values, data, calcParams){\n debugger;\n return 123;\n}\n\nvar cellFormatter = function(cell, formatterParams, onRendered){\n let value = cell.getValue();\n if (value > formatterParams.threshold) {\n cell.getElement().style.backgroundColor = formatterParams.aboveColor; \n } else { \n cell.getElement().style.backgroundColor = formatterParams.belowColor; \n } \n\n return value + ((formatterParams.unit) ? ` ${formatterParams.unit}` : '');\n}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":690,"y":220,"wires":[[]]},{"id":"11346c8e6b90e622","type":"ui_group","name":"Calculation Functions","tab":"e33f9653df8ec25d","order":1,"disp":true,"width":"10","collapse":true},{"id":"e33f9653df8ec25d","type":"ui_tab","name":"Table","icon":"dashboard","disabled":false,"hidden":false}]
```
---
### Author:  [@sheminasalam](https://discourse.nodered.org/u/sheminasalam)
#### Post date: [11 September 2021 16:36 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/16 "2021-09-11T16:36:01Z")
This worked, Thanks
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [25 September 2021 16:36 UTC](https://discourse.nodered.org/t/ui-table-percentage-calculation/50741/17 "2021-09-25T16:36:30Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.