I have a Metric control in Canvas which uses this query:

```
SELECT
SUM(AbstractData.no_of_assets_with_SIR) * 100.0 / SUM(AbstractData.count_total_assets) as percentage
FROM
cpg_abstract_dev_project_kpi
```

Now I need to do the same thing as an Visualization on a Dashboard instead and I'm struggling with achieving the same result.

My approach so far has been to use a Metrics control in visualization, aggregate using SUM on the field "AbstractData.no_of_assets_with_SIR" and then add a script in JSON input to do the rest of the calculation (multiply "_value" with 100 and then divide with the sum of the field AbstractData.count_total_assets). So far I have not been able to figure out how to get the sum of the field AbstractData.count_total_assets in the script. If not possible to calculate that sum directly in the script, I'm guessing that I would need yet another SUM aggregation, but I canĀ“t see how I can do that in the Metric control. Any push in the right direction to solve this would be much appropriated!

This is my script so far:

{

"script": {

"lang": "painless",

"inline": "double total = 0.0; total = _value * 100; return total;"

}

}

I would also need to divide the "total" in the script with the sum of the field AbstractData.count_total_assets

Best regards

/Magnus