Tableau leverages the power of your database and sends dynamic SQL or MDX statements directly to the database instead of importing all the data. Alas, most databases do not support the Median as an aggregation which is why it is not in the list. For connections to data in Oracle 10g, Tableau provides support for the Median aggregation.
But if my datasource is an Excel 2007 spreadsheet, this is not do-able, correct?
I'm having the same problem as well and my datasource is from excel as well. Any solution would be greatfully appreciated.
Hey, Dan is right that Excel and other local file data sources such as Access and Text files do not support the RAWSQL functions. You can extract the data by selecting Data > Extract and save it as a .tde files (Tableau Data Extract). While the data extract does not support the Median aggregation, you can pass other SQL queries directly to the database. Only the Oracle 10g connections will support a Median aggregation in the way you are suggesting though.
This is an old thread but that's okay.
I'm also wondering how to create a calculated field and use other aggregate functions such a median, mode, etc. All I see are things like SUM, AVG, MAX, MIN, etc.
And here's why I need this: when I look at sales data and I want to calculate an average order value (Total Sales / Total Order Count) I sometimes get a number that is skewed high and not representative of the data set due to one or two really large orders.
I'd prefer to use the Median to see if I get a number more representative of the data set. Filtering out outliers by any means doesn't really work for me, as it will skew my other metrics on the page.