I have 2 tables. Table 1 is transaction table which will have registrations from various cities. Table 2 is static table which has city-wise expected Registrations. I wanted to display overall % Registration which would be Total Registrations/ (sum of expected registrations for each city). I am trying to do this using custom expression but can’t figure out. I can easily write a native query and do this but just wanted to avoid as I am expecting lot of filters in future.
Ideally if I am able to do a expression like count/sum(distinct expected_registrations) it would give me the result but I think this is not allowed in custom expression. Any tricks?
Hi @jiwnaiakbar
I’m just trying to understand what expected_registrations is - the column type.
Since from your text, I would guess that Count / Distinct([expected_registrations]) should work, but that translates the Distinct() function in count(distinct column), which is probably not what you’re after.
I don’t remember seeing a request for sum(distinct column), which perhaps should be handled in Metabase like Sum(Distinct([column])) - you’re welcome to open a feature request: https://github.com/metabase/metabase/issues/new/choose
Hi @flamber thnx for the reply. Count / Distinct([expected_registrations]) will not work because I will have multiple cities with each city having expected_registrations number.
Below is sample of both the tables:
Table 1
CityId
City Name
ExpectED Registrations
1
City 1
10
2
City 2
5
3
City 3
15
4
City 4
6
5
City 5
50
Registration ID
Name
City
1
User1
City 1
2
User2
City 2
3
User3
City 1
4
User4
City 1
5
User5
City 3
6
User6
City 4
7
User7
City 1
8
User8
City 2
9
User9
City 5
10
User10
City 1
So Overall % Registration would be Count(Registration ID)/sum(10+5+15+6+50) i.e. 11%
