First off all I would like to thank you for creating such an incredible piece of software and making it available as open source: THANK YOU.
I have created a question that is grouped and has several sum()ed fields. I would like to add a few additional fields that calculate percentages based on those.
I added custom fields, but they don’t show up. It seems like they are added to the original table, but I would like to add them to the grouped table.
Basically, I want to know the percentage of people who signed up. For the /, it should display 3% (100/2205*67 = 3).
Is there a way to achieve this or do I have to do a custom sql query?
I think you might be running into an issue we're tracking which causes custom fields to lose their type info, meaning that they're not correctly being treated as numeric fields, so Metabase is filtering them out when you try to choose them as the target of your aggregation:
Hi @kimcha were you ever able to resolve this issue? Im using V.32.4 - built a question that counts then pivots the values to create two columns and would then like to create a custom field that expresses a ratio of the two values. I tried creating a second question that used the first as its data source but the custom filed option doent seem to be available when the data source is a “saved question”. Any pointers appreciated.
Hi @brianwilson
I would recommend that you upgrade to 0.32.8 - several issues with earlier 0.32.x
I’m not quite sure I understand where your problem is - Pivot or Custom Field or Saved Question?
Can you explain a bit more - or provide a screenshot if that makes it easier?
Dear @flamber I create a question that pivots the data correctly:
State
2017
2018
MN
1
4
MA
2
2
NY
10
100
I want to add a custom field to the pivot that calculates the ratio of the two numeric columns:
| State | 2017 | 2018 | ratio |
|---|---|---|---|
| MN | 1 | 4 | 0.25 |
| MA | 2 | 2 | 1 |
| NY | 10 | 100 | 0.1 |
but the pivot columns 2017 & 2018 are not listed as possible fileds in the Custom Field option, hence why I tried to use the pivot question as the data source for a new question, hoping that the columns would then be available to create a custom expression.
@brianwilson
Okay, so Custom Fields currently only lets you use numeric fields. I’m guessing you’re getting the year from a date field.
It sounds like you’re asking for something similar to sub-totals (but not quite).
I think you need to create a Native Query to do what you want. Which would involve doing sub-queries and union.
Just an example: https://stackoverflow.com/questions/7925748/can-you-subtotal-rows-and-or-columns-in-a-pivot-table
