How to draw mean line in Metabase?

Hello, everyone

How can I draw a line to describe the mean of a distribution in Metabase?

In this specific case, I want to get the X-Axis to display the time range and the Y-Axis to display the number of deadlines to be met within this time range. I also want to draw an line to describe the mean, so that it’s possible to know if more deadlines than normal as coming up in the next weeks.

So far I could build this:

However, this is not optimal, because Metabase automatically sets a secondary Y-Axis, which leads the values to be displayed in a quite counterintuitive way.

The Query is wrote is:

SELECT thing_one.woche, thing_one.venture, thing_one.anzahl_week, thing_two.average
FROM (SELECT WEEK(_deadline._end_date) as woche, 
    field__a7e1bc2126464685c29c55d664bbb34ee955e30d.value as venture,
    COUNT(*) anzahl_week
FROM _deadline
LEFT JOIN _deadlinetype ON _deadline._type_oid = _deadlinetype._oid 
LEFT JOIN akten on akten.oid = _deadline._akte_oid
LEFT JOIN field__a7e1bc2126464685c29c55d664bbb34ee955e30d ON field__a7e1bc2126464685c29c55d664bbb34ee955e30d._akte_oid = _deadline._akte_oid
        
WHERE testakte !="Ja" 
    AND testakte NOT LIKE '%test%'
    [[AND {{Fristart}}]]
    [[AND {{venture}}]]
    [[AND {{zeitspanne}}]]
    
        
GROUP BY WEEK(_deadline._end_date) ) AS thing_one 

LEFT JOIN (SELECT weekly.venture, AVG(weekly.anzahl_week) as average
FROM (
SELECT WEEK(_deadline._end_date) as woche, 
    field__a7e1bc2126464685c29c55d664bbb34ee955e30d.value as venture,
    COUNT(*) anzahl_week
FROM _deadline
LEFT JOIN _deadlinetype ON _deadline._type_oid = _deadlinetype._oid 
LEFT JOIN akten on akten.oid = _deadline._akte_oid
LEFT JOIN field__a7e1bc2126464685c29c55d664bbb34ee955e30d ON field__a7e1bc2126464685c29c55d664bbb34ee955e30d._akte_oid = _deadline._akte_oid
        
WHERE testakte !="Ja" 
    AND testakte NOT LIKE '%test%'
    [[AND {{Fristart}}]]
    [[AND {{venture}}]]
    
        
GROUP BY WEEK(_deadline._end_date) ) as weekly ) as thing_two ON thing_one.venture = thing_two.venture

So, I’d like to know:

  1. How can a write a mean line (and display it correcly in Metabase?)

  2. Is there a simpler way to write the query? (I saw this post and this post in Metabase’s Discussion page in Github, but I don’t really know what came out of it.

Thanks in advance for any help.

So, under Axis there is the option to shut down the secondary Y-Axis, so this point of my question was quite a dumb one.

I’d still like to know if there is a way to simplify my query though.

Hi @celioxf
It’s currently not possible to create your own trend-line - it is generated by Metabase.
And you might find better help to optimize your query in a forum specific to your database or perhaps something like stackoverflow.com

1 Like