Calculating ratio in transformations

[lynxx]

• Grafana version Grafana v11.1.0

• Operating system: OpenSUSY tumbleweed

• What: I am comparing the measurements of two data sources. I would like to display the ratio of the difference between these measurements by date outside a range. ie. i have departure delays for two sources, comparing them together I get a difference of departure delays for a bus network, I want to see the ratio of measurements (difference of departure delays) by date that are outside an acceptable level, say 10 sec

• How: count the number of measurements outside the range, and divide by the total number of measurements. This is how I would create the dataframe in SQL and display it from here.

select
    "Date",
    sum(case when abs(departure_delay_diff) > 10 then 1 else 0 end) / count(departure_delay_diff)::float as arr_ratio_above10
from table
group by "Date"

1. I’ve tried using transformations to calculate the fields and filter what I need, but the act of filtering/grouping removes the ability to calculate the total counts, to then calculate the ratio
2. I’ve researched to see if I can use multiple queries (duplicated) to then merge back in the total in order to calculate the ratio. The documentation suggests that it can be done, but I don’t seem to be able to select a specific query. I don’t see the filter icon on the top right in any of my transformations
   
   
   
   Here you can see I have 2 queries (duplicate), and a few transformations, to select the fields and filter by my criteria. This gives me the count of measurements with my criteria, but I need the total (excluding the criteria) to determine the ratio
   
   

• What happened? I am unable to create the required calculation to construct the dataframe to visualise

• What did you expect to happen? I expected there to be a way to configure the transformations to create the calculations required.

  • I thought there might have been a way that I could duplicate the query.
  • Use query A to filter for my criteria and then group by date to get the count of measurements that agreed with the criteria.
  • Then merge in the result of query B with the same grouping (without the filtering).
  • This would allow me to create the fields needed to calculate the ratio

My Question is:
Am I missing something (a setting in config, or somethign else) that allows me to use multiple queries and transformations to create the calculated field required for my use case?
Is there another way I am not thikngin off?