Custom Visualizations in Databricks AI/BI Dashboards: Build a Dumbbell Chart

[pstyld]

Many dashboards eventually answer the same questions: 

• Are we hitting our targets?
• Where are we falling short?

A dumbbell chart answers these questions at a glance.

• For each category, it plots two values, an actual and a target, as dots connected by a line.
• The distance between the markers shows the size of the gap, making it easy to spot where actuals are above or below target.

Databricks AI/BI dashboards do not have a built-in dumbbell chart widget, so you will build one with a custom visualization (currently Public Preview) widget. Custom visualizations use the Vega-Lite library to render charts from a JSON specification. The visualization reads directly from your AI/BI dashboard dataset, so you can go beyond the built-in chart types while continuing to work with your governed data in Databricks.

We will consider the following scenario: You've been asked to report Regional Sales Performance vs. Targets. You decide to use a dumbbell chart to clearly show the gap between actual sales and targets.

You'll build the following visualization in an AI/BI dashboard:

Create the Custom Dumbbell Chart

Feel free to follow along in Databricks Free Edition.

Step 1: Create the dashboard

1. In the main navigation bar, select Dashboards.
2. Click Create dashboard.
3. At the top left of the dashboard definition panel, you'll see a placeholder name like New Dashboard 20XX-01-01 12:00:00
4. Click the placeholder name and change it to FirstName-LastInitials - Custom Dumbbell
5. At the top right, select a Serverless SQL Warehouse.

Step 2: Create the data

We start from a small, pre-aggregated dataset with one row per region.

To keep this post simple, we build that data inline with a VALUES clause instead of creating a detailed source table and aggregating it. In a real project, you would aggregate a detailed table or pull from an existing aggregated table.

1. In the Dashboard, select the Data tab.
2. Choose Add SQL dataset.
3. Add the following SQL code: 

   SELECT
     region,
     actual,
     target,
     actual - target AS gap,
     CASE WHEN actual >= target THEN 'At or above target' ELSE 'Below target' END AS status
   FROM VALUES
     ('West',      92000, 70000),
     ('Southeast', 73000, 81000),
     ('Midwest',   68000, 64000),
     ('Northeast', 60000, 66000),
     ('Pacific',   38000, 75000),
     ('Southwest', 47000, 52000),
     ('Mountain',  51000, 48000)
     AS t(region, actual, target);
   

4. Rename the dataset to dumbbell_summary (This is the dataset name. Inside the spec, the visualization binds to its dataset through the reserved name databricks_query, not the name you choose here).
5. Select Run to execute the query and view the data. Your table will look like this:

region	actual	target	gap	status
West	92000	70000	22000	At or above target
Southeast	73000	81000	-8000	Below target
Midwest	68000	64000	4000	At or above target
Northeast	60000	66000	-6000	Below target
Pacific	38000	75000	-37000	Below target
Southwest	47000	52000	-5000	Below target
Mountain	51000	48000	3000	At or above target

The columns that matter for the chart:

• region is the category, one per row.
• actual and target are the two values the dumbbell compares. Each becomes a dot, and the line between them is the gap.
• gap is actual - target, and it drives the sort order and the labels.
• status flags each region as At or above target or Below target, and it drives the connector color.

Step 3: Build the custom visualization

In the dashboard UI:

1. Select the Untitled page.
2. Add a visualization widget to the canvas. 
3. Expand its width across ¾ of the canvas and to a height of about 8 blocks.
4. Add the title: Regional Sales Performance vs. Target
5. Add the description: Compare actual sales to targets across regions. Quickly identify which regions are exceeding expectations, where performance is falling short, and the size of each gap.
6. In the visualization field, under the Advanced section, select Custom Viz. (Here you will see a default error - The dashboard is trying to reference fields that do not yet exist. This is expected). 
7. In the Fields section, add these columns (the names are how the spec references them): 

• actual
• gap
• target
• region
• status

With your widget set, now paste the spec below into the Vega-Lite Specification editor:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v6.json",
  "width": "container",
  "height": "container",
  "data": { "name": "databricks_query" },
  "encoding": {
    "y": {
      "field": "region",
      "type": "nominal",
      "sort": { "op": "min", "field": "gap", "order": "descending" },
      "axis": { "title": null }
    }
  },
  "layer": [
    {
      "mark": { "type": "rule", "size": 14 },
      "encoding": {
        "x": { "field": "target", "type": "quantitative", "axis": { "title": "Sales ($)" } },
        "x2": { "field": "actual" },
        "color": {
          "field": "status",
          "type": "nominal",
          "scale": { "domain": ["At or above target", "Below target"], "range": ["#00A972", "#FF5F46"] },
          "legend": { "title": null, "orient": "top" }
        },
        "tooltip": [
          { "field": "region", "type": "nominal", "title": "Region" },
          { "field": "actual", "type": "quantitative", "title": "Actual", "format": "$,.0f" },
          { "field": "target", "type": "quantitative", "title": "Target", "format": "$,.0f" },
          { "field": "gap", "type": "quantitative", "title": "Gap", "format": "+$,.0f" },
          { "field": "status", "type": "nominal", "title": "Status" }
        ]
      }
    },
    {
      "transform": [
        { "fold": ["target", "actual"], "as": ["metric", "value"] },
        { "calculate": "datum.metric === 'actual' ? 'Actual' : 'Target'", "as": "Measure" }
      ],
      "mark": { "type": "point", "size": 260, "strokeWidth": 3, "opacity": 1 },
      "encoding": {
        "x": { "field": "value", "type": "quantitative" },
        "stroke": {
          "field": "Measure",
          "type": "nominal",
          "scale": { "domain": ["Actual", "Target"], "range": ["#1B5162", "#90A5B1"] },
          "legend": { "title": null, "orient": "top" }
        },
        "fill": {
          "field": "Measure",
          "type": "nominal",
          "scale": { "domain": ["Actual", "Target"], "range": ["#1B5162", "#FFFFFF"] },
          "legend": { "title": null, "orient": "top" }
        },
        "tooltip": [
          { "field": "region", "type": "nominal", "title": "Region" },
          { "field": "actual", "type": "quantitative", "title": "Actual", "format": "$,.0f" },
          { "field": "target", "type": "quantitative", "title": "Target", "format": "$,.0f" },
          { "field": "gap", "type": "quantitative", "title": "Gap", "format": "+$,.0f" },
          { "field": "status", "type": "nominal", "title": "Status" }
        ]
      }
    },
    {
      "transform": [
        { "calculate": "(datum.actual + datum.target) / 2", "as": "label_x" }
      ],
      "mark": { "type": "text", "align": "center", "baseline": "bottom", "dy": -10, "fontSize": 12, "fontWeight": "bold" },
      "encoding": {
        "x": { "field": "label_x", "type": "quantitative" },
        "text": { "field": "gap", "type": "quantitative", "format": "+$,.0f" },
        "color": {
          "field": "status",
          "type": "nominal",
          "scale": { "domain": ["At or above target", "Below target"], "range": ["#00A972", "#FF5F46"] },
          "legend": null
        }
      }
    }
  ],
  "resolve": { "scale": { "x": "shared" } },
  "config": {
    "autosize": { "type": "fit", "contains": "padding" },
    "view": { "stroke": null }
  }
}

You do not have to write a spec like this by hand. This one was generated and refined with Genie Code.

How the spec works

• Binding - The chart reads directly from the dashboard dataset through {"name": "databricks_query"}, so it always reflects the current query results.
• Sort and axes - region sits on the y-axis, sorted by gap in descending order, so the ranking reorders as values change.
• Reshape - actual and target start in separate columns. A fold transform reshapes them so each value can be plotted as its own dot.
• Layers - Three marks create the chart: a line connecting actual and target, dots for the two values, and text for the gap label.
• Actual vs. target - Actual is shown as a solid dot and Target as a hollow ring, making the two values easy to distinguish.
• Color - The connector and gap label use the status field to show green for at or above target and red for below target.
• Labels - The gap is displayed at the midpoint between the dots and formatted as a dollar value.
• Tooltip - Hovering shows region, actual, target, gap, and status, all pulled directly from the data.
• Everything is field-driven - No data values are hard-coded. The chart automatically recomputes as the underlying data changes, which is what the next section demonstrates.

Step 4: Polish with Genie Code

As mentioned earlier, you do not have to write or update this JSON by hand. Genie Code can generate a first draft or refine an existing spec in natural language. 

For example, for our final clean up let's use Genie Code. 

Select the Genie Code icon, select your custom viz, and describe the changes that you want (output can vary):

This visualization is a Vega-Lite custom viz. Edit the JSON spec directly. Apply these formatting changes and leave everything else unchanged: 
1. Remove the vertical gridlines on the x-axis. 
2. Set the x-axis title ("Sales ($)") to font size 14. 
3. Set the axis tick labels, the x-axis values and the region names on the y-axis, to font size 13. 
4. Format the x-axis tick values with a thousands separator. 
5. Set the legend label font size to 13.

See it update dynamically with new data

Here is where the dynamic design pays off. 

Every channel keys off a column, sorted by gap, colored by status, labeled from gap, so new data just flows in.

1. Go back to the Data tab.
2. Add these two rows to the bottom of your VALUES list, right before the AS t(...) line. Then select Run to view the updated data: 

   , ('Atlantic', 81000, 64000), 
   ('Central', 45000, 72000)

3. Switch back to your dashboard and click Refresh.

The two new regions drop into the right sorted position by their gap, connectors colored by status, dots styled the same, and gap labels formatted, all with no change to the spec. 

Build it once, and it stays correct as the source data is updated.

Your turn! Can you make it better?

The spec above is one solution, and there is plenty of room to improve it. Try it in Databricks Free Edition and see what you can do. Made it better? Share your spec and a screenshot in the comments. The best ideas help everyone learn new techniques for building custom visualizations.

Learn more

New to AI/BI Dashboards? The AI/BI for Data Analysts course on Databricks Academy covers building interactive dashboards, creating visualizations, using Genie Code for AI development, publishing, scheduling, and more.

• Build a Pareto Chart with Vega-Lite (part 1 of this series)
• Custom visualizations in AI/BI dashboards
• Vega-Lite documentation
• Genie Code
• Databricks Free Edition

Special thanks to Louis Frolio, Maggie Li, and Matthew McCoy for technical review.