Chart Type - Bullet Charts

I love a great benchmark or comparison to goals. One solution that Kyle on my team came up with was a bullet chart. I standardized this with a Parameterized SQL Snippet to do all the transformation heavy lifting for you 🙂

Name: bullet_chart(table,value_field,benchmark_field,dimension_field)

select
  case
    when [value_field] < [benchmark_field]
      then [benchmark_field] - [value_field]
    else null
  end as value
  , 'to benchmark' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
union all
select
  case
    when [value_field] > [benchmark_field]
      then [benchmark_field]
    else [value_field]
  end as value
  , '[value_field]' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
union all
select
  case
    when [value_field] >= [benchmark_field]
      then [value_field] - [benchmark_field]
    else null
  end as value
  , 'passed benchmark' as label
  , [dimension_field]
  , rank() over(order by [value_field]) as ord
from
  [table]
order by 2 desc, 4 desc

Updated 03-02-2023

CDT