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%
@jiwnaiakbar Yeah, I can definitely see the use case - and you have described it very well with the table, so please open a feature request on that.
Done. 
1 Like
