How would you calculate the median of a dataset in a Tableau table? How to replicate an Excel array formula in Tableau?
- Tableau FAQs
- December 11, 2018
This post will give you two ways to calculate the median of a dataset in Tableau and Datameer
Use Datameer as an alternative to calculate the median of a dataset in Tableau
Datameer is an easy-to-use transformation platform that requires little to no coding skills . This makes it easy for non-technical people to navigate its UI without hassle and build transformations and SQL complex logic with just a few clicks.
In Datameer, you can calculate the median of any dataset in split seconds without any code!
Impossible, you say? I think not.
It is possible through the “Aggregate ” feature that Datameer offers. On the aggregate interface, you can calculate the median of a dataset as well as the mode, sum, count, count distinct, and a host of SQL functions with a single click!
Ready to create the magic? Watch the video below to see how it works
Calculating median of a dataset using Tableau
With Excel, this is called an array formula. To calculate the median in Tableau, you can use the WINDOW_MEDIAN () function.
This is how it works internally:
WINDOW_MEDIAN(SUM([SalesYTD]), FIRST(), LAST())
This formula returns the median of the expression within the window. The window is defined using offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition.
In Tableau, If the start and end are omitted, the entire partition is used.
WINDOW_MEDIAN( – It is the start of expression
SUM([SalesYTD]) – This is the sum for the selected field; this is a required expression. Here you can also use any other functions, for example, AVG.
FIRST(), LAST()) – These are two required arguments; if you want to calculate the median over the entire data window, leave it unchanged. If you need to count it, for example, from the second line, then you can specify FIRST () – 1, etc.
Up Next:
Read Can you recreate a process flowchart normally built in a tool like Visio but in Tableau?