Divide 2 metrics / Numbers (Ratio)

Get Help
#1

Hello,
I have some questions in my dashboard that their final result is a number.
I want to create a question that represents the ratio between them.
For example - I have a question that displays “how many users registered this month” and another question that shows “How many users registered this month + added their phone number”…
How can I create a question that shows the ratio?
I tried to set those questions as metrics but that didn’t help. it sounds like a pretty common usecase.

Capturexxx
Capturexxx.PNG1023×855 15.6 KB

Any help guys?

Thanks,
Yoav

#2

I think you need to do it as a custom query. First, convert the questions to SQL, copying the SQL to a text editor.
Then create a new question with a native query.
It’s a bit fiddly as you can’t just divide one result by another. Mine was:

SELECT a.ordersum / b.prodcount FROM
(SELECT sum("PUBLIC"."ORDERS"."TOTAL") AS ordersum
FROM "PUBLIC"."ORDERS") a
CROSS JOIN 
(SELECT count(*) AS prodcount
FROM "PUBLIC"."PRODUCTS") b
3 Likes
#3

Anyone have any luck with this? Wondering if metabase has come up with anything since this questions was posted.

#4

@elodge1 It should be possible with 0.33.0, but there’s an issue, where all the columns of joined tables are not available in the Custom Expression, so currently the solution is still SQL.

#5

Yeah, I just filed https://github.com/metabase/metabase/issues/10759 as well, which is specifically about the limitation of joined columns from saved questions not showing up, which might be the same underlying issue as https://github.com/metabase/metabase/issues/6164.

I’m going to see if we can address all of these types of issues around custom columns and expressions in 0.34.

1 Like
#6

Found an easy solution for this, for example: You want ratio of number of women / total population

  1. create a question to get number of women and then click on “view in SQL”
  2. create a question to get number of total pop. and then click on “view in SQL”
  3. In SQL editor of the first question type this: select(1.0*(sql of question 1))/(sql of question 2)
  4. click on run.
    Voilà!!!
    Pd: the 1.0 bit is to prevent issues, found this here
2 Likes
#7

This was helpful, thanks!