# Calculating (a bit special) ratio using Grafana transformations

• What Grafana version and what operating system are you using?

Grafana 9.0.6 inside Docker.

• What are you trying to achieve?

Tldr: I want to calculate a ratio (calculating it is a bit special so read below) of all rows where a value from a column from one datasource is within the interval provided by two other columns from another datasource.

Data comes from two PostgreSQL databases. First datasource contains columns `id` and `target`, and the second one contains `id`, `low` and `high`. Rows from both datasources should be joined/merged depending on their id.

Then, I want to calculate a ratio of rows where `target` is within the interval provided by `low` and `high`, where rows with `target` right on the edge of an interval count as 50%. So, where `low < target AND target < high`, count 2; where `low = target OR target = high`, count 1; otherwise, count 0. Then, divide the result by a (number of rows * 2) and display that in a Grafana gauge.

If everything was in a single PostgreSQL database, I could use this as a query:

``````SELECT
SUM(CASE
WHEN low < target AND target < high THEN 2
WHEN low = target OR target = high THEN 1
ELSE 0
END)::float / COUNT(intervals.id) / 2 AS "rate"

FROM intervals
-- This table contains columns id, low, high

INNER JOIN targets
ON intervals.id = targets.id
-- This table contains columns id, target

WHERE
-- Some conditions...

ORDER BY 1
``````

However, I cannot do this, because `intervals` and `targets` tables are not in the same database. I need a way to do this using Grafana transformations or another similar Grafana feature that works across multiple datasources.

• How are you trying to achieve it?

I have two PostgreSQL queries, one for each datasource, that retrieve required columns:

``````-- Datasource 1
SELECT id, target FROM targets
WHERE -- Some conditions...
ORDER BY 1
``````
``````-- Datasource 2
SELECT id, low, high FROM intervals
WHERE -- Some conditions...
ORDER BY 1
``````

Then, I have to use Grafana features to merge those two queries and calculate ratio as I described above. With Grafana transformations, I thought I could do something like:

1. Merge
2. Add field `type` from value mapping
• Where `low < target AND target < high` → 2
• Where `low = target OR target = high` → 1
• Otherwise → 0
3. Organize fields
• Hide everything except `type` field
4. Reduce
• Mode: Series to rows
• Calculations: Total, Count
5. Add field `rate` from calculation
• Mode: Binary operation
• Operation: `Count / Total / 2`
• Replace all fields: Yes

And then display “rate” as a gauge.

• What happened?

Nothing like “Add field from value mapping” transformation exists. The closest is existing “Value mapping” panel feature, but it cannot be integrated with transformations to perform steps 2+, and has very limited conditions (everything needs to be based on exact values or exact range start/end, while I need to have this based on value from another field). The closest transformation is “Rename by regex”, but it only affects field names, and I still couldn’t use it for dynamic conditions.

The other (smaller) problem is that “Binary operation” from “Add field from calculation” transformation can only perform one calculation/operation at a time. This means I would have to split step 5 into two separate transformations, one to calculate `Count / Total` and the other to calculate `Previous result / 2`. This is only an inconvenience, but I think it would still be very useful to perform multiple operations in one transformation.

• What did you expect to happen?

I expect to be able to perform these calculations using Grafana.

I guess Grafana would need to add “Value mapping” mode to the “Add field from calculation” transformation that would allow doing what I described above, and to make “Binary operation” mode more powerful to accept more “advanced” calculations.

Or is there any other was to do this?

• Did you follow any online instructions? If so, what is the URL?

Documentation for Grafana transformations.