How to reference another column value in metricFont expression?

Elastic Stack Kibana
1

Hi,
I have a table that returns only one row with two columns: carrier and sum_orders.

I'm currently displaying sum_orders in a metric element. I would like to change the font color of the number based on the value in the carrier column, which can be one of the following: 1, 2, 3, 4, or 5.

How can I reference the carrier value in the expression?

Here is the query I’m using:

kibana
| selectFilter
| essql query="
  SELECT
    case
        when delivery_string like '%Transp%' then 4
        when delivery_string like '%DH%' then 5
        when delivery_string like '%UP%' then 3
        when delivery_string like '%CO%' then 2
        when delivery_string like '%RE%' then 1
        else 4
    end as carrier,
    sum(qty_ordered) as sum_ordered

  FROM \"ehs-outbound\"
  WHERE 1 = 1
    AND order_type = 'DL1'
  GROUP BY 1
  ORDER BY 2
  LIMIT 1
"

| math "sum_ordered"
| metric "sum_ordered"

  metricFont={font align="center" color={switch {case if={eq 1} then="#fd986f"} {case if={eq 2} then="#f5cc5d"} {case if={eq 3} then="#ff0000"} {case if={eq 4} then="#ff8000"} {case if={eq 5} then="#808000"} default="#000000"} family="'Open Sans', Helvetica, Arial, sans-serif" italic=false size=96 underline=false weight="normal"} 

  labelFont={font size=1 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"

| render

The switch statement in the metricFont currently references the sum_orders value. How can I make it reference the carrier value instead?

Thanks in advance!