Hello, everyone! I've been spinning my wheels for awhile and would love some help! Basically, I have this table result:
The "Number of Buildings" column is a distinct count of a variable, BUILDING_ID, which is then grouped by country and asset type. The editor is here:
After the "Number of Buildings" column, I would like to add another column where each value is the percentage amount that the associated "Number of Buildings" value represents of the total grouping. In other words, I want my new column to be "Number of Buildings"/SUM("Number of Buildings")... I think that's right. In my screenshot, there are a total of 18 buildings across the rows - Hong Kong, with 6 buildings, should have a value of 33 in this new column, which is saying that those 6 buildings represent 33% of the 18 buildings in the grouping. How do I make this happen?
Hi @chadwicke
You would need to create two questions - or use SQL - so you have the total amount of buildings that you can then use per row in a join.
I see. I'm experimenting with the editor so that I can provider the end user with the ability to easily filter. So the result I'm looking for cannot be replicated with the editor, alone, maintaining the ability of the business user to filter by column? Or am I misunderstanding your response?
@chadwicke If you were to do what you're trying to in SQL, how would you write the query?
You are aggregating the data, but where should the total (18) number come from, which you want to use for each row?
That's what I mean by it would require multiple queries (questions) to do this.
There's a request for adding this functionality in the interface:
https://github.com/metabase/metabase/issues/2120 - upvote by clicking 
on the first post
Well, normally I would do something like:
COUNT() AS "Number of Buildings",
COUNT()/ SUM(COUNT(*)) over ()*100 AS "Percentage: Buildings"
I just kind of assumed that the second line there (the count divided by the sum of the counts) could be written as a custom expression to create the column, but it's sounding like that's not the case - correct?
@chadwicke Metabase currently does not support windowing, since many databases didn't support this several years ago. It's something that we really want to add, but it's tricky when it has to work for as many database types as Metabase supports. It would also fix several problems we have with Cumulative Sum/Count.
The Custom Expressions are Metabase functions, not plain query input.
