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 is this graph I put together to track COVID-19 cases and deaths in the USA:
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:
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’re comparing our eCommerce site’s unique visitors and relative 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 how we alias total cases and deaths as total_cases and total_deaths respectively.
Step 2: Modify our visualization to add a second Y axis
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:
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.
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’s two more resources to help you build Grafana dashboards like a pro:
#1 Grafana Webinar
Join me on May 20 at 10am PT/1pm ET/4pm GMT where I’ll demo how to:
- Use alerts effectively when monitoring metrics in Grafana
- Define alert rules for your panels and dashboards
- Configure different notification channels, like Slack and email
- 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.
#2 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
Top comments (0)