How to Build More Accurate Grafana Trend Lines: Plot Two Variables with Series-Override

Avthar Sewrathan -

Plotting multiple variables on one graph and want to compare trends? Learn how to use Grafana’s series-override feature to solve the problem of distorted scale.

Problem: Skewed Trends Due to Differences in Data Scale

Many times, we want to plot two variables on the same graph (a useful feature of viz tools like Grafana), but, run into one big problem: the scale of one of the variables distorts the trend line of the other variable.

Case in point, this is a graph I put together to track COVID-19 cases and deaths in the USA:

The scale of total_cases makes the total_deaths trend line look flat, when it's actually growing

As you can see, the scale of the total cases makes the trend line for deaths look flat, even though it’s actually growing rapidly, as we can see from the graph which plots only COVID-19-related deaths:

USA COVID-19 reported deaths over time (with a more obvious upward trend line)

Viewing two related data points in one graph is extremely useful to create informationally dense dashboards and compare related variables, but distorted trends can have large consequences - whether we view the COVID fatality situation more optimistically than we should, or if we inaccurately compare our eCommerce site’s unique visitors relative to session crashes.

We need a way to more accurately represent the trends of both variables, while still plotting them on the same axis.

Solution: Two Y Axes!

The solution is to use a different Y axis for each variable on our graph. Continuing with my COVID-19 example, this means one for the total cases variable and one for the total deaths variable, as shown in the graph below:

Here, we use two Y axes: one for COVID-19 total cases, on the left, and one for total deaths, on the right

Each axis has their own scale, allowing us to more accurately see the growth of each trend line without the scale of one variable (e.g., total volume of reported cases) impacting how another variable (e.g., growing number deaths) appears.

Try it yourself: Implementation in Grafana with Series Override

In this post, I'll show you how to use Grafana’s series override feature to implement two Y axes (and, thus, solve our two-trend line problem).

We’ll use the example of charting the spread of COVID-19 cases and deaths in the USA, but the concepts apply to any dataset you’d like to visualize in Grafana. We’ll get our COVID-19 data from the New York Times’ public dataset.

Prerequisites

To replicate the graph I’ll create in the following steps, you’ll need a:

  • Grafana instance
  • TimescaleDB database, loaded with the NYT COVID-19 data.
  • PostgreSQL datasource, with TimescaleDB enabled, connected to your Grafana instance. See here to get this setup.
  • Grafana panel with Graph visualization using the PostgreSQL database with the COVID data as the data source.

Step 1: Create two series

Plotting multiple series in one panel is a handy Grafana feature. Let’s create two series, one for COVID-19 cases and the other for COVID-19 deaths:

SELECT date as "time", sum (cases) as total_cases, sum (deaths) as total_deaths
FROM states
GROUP BY time
ORDER BY time;

Notice: we alias "total cases" and "deaths" as total_cases and total_deaths, respectively.

Step 2: Modify our visualization to add a second Y axis

Grafana Visualization settings for adding a second Y-Axis to our graph

First, navigate to the visualization panel (pictured above) and select the Add series override button.

Then, we select the name of the series we'd like to override, “total_deaths” from the drop down menu. Then, to associate the series with the second Y axis, we select the ‘plus’ button and then select Y-Axis 2, as shown below:

How to find the Y-Axis 2 option

When we navigate down to the Axes section, we see Left Y and Right Y, where we customize the units and scale for each axis.

In our case, we’ll leave the units as short and the scale as linear, since those defaults work for the scalar quantities in our COVID dataset.

Finally, we save the graph and refresh. We should now see both variables, total cases and deaths, plotted on the same graph, but with differently scaled axes.

Trend line before (singular axis) and after (two axes). 

Notice: we more clearly see how quickly COVID-19 deaths in the USA are growing, which was difficult to discern in the original graph (where deaths were plotted with total COVID-19 cases on the same Y axis).

That’s it! We’ve successfully created a graph with two Y axes, using series-override 🎉.

Learn More

Found this tutorial useful? Here are two more resources to help you build Grafana dashboards like a pro:

Grafana Webinar

Join me on April 22nd at 10am PT/1pm ET/4pm GMT where I’ll demo how to:

  • Create 5+ different visualizations for data from IoT, infrastructure monitoring, and public datasets
  • Combine various data types, including geo-spatial and time-series data, in our dashboards
  • Take my demo and customize it for your project, team, or organization

I’ll focus on code and step-by-step live demos – and I and my dashboarding expert colleagues will be available to answer questions throughout the session, plus share ample resources and technical documentation.

Signup even if you’re unable to attend live, and I’ll make sure you receive the recording, slides, and resources - and answer any questions you may have along the way.

All-in-One Grafana Tutorial

We’ve compiled all our tutorials, tips, and tricks for visualizing PostgreSQL data in Grafana into this one doc.

  • You’ll find everything from how to create visuals for Prometheus metrics to how to visualize geo-spatial data using a World Map.
  • Check it out here.