Recently, I came across a problem where I had to find the median value of a measure. Now although this sounds like the use of a simple median() function in tableau, unfortunately that wasn’t the case.
Again, for the purpose of simplicity, I'll be using the superstore data.
For this use case, I need to show the quantity for each customer and order id and compare it with the median quantity value of all customers.
If you check the superstore data, you'll notice that the data is on an order id level and any aggregation that happens on the data happens on that level.
Now, the problem is, I need to find the median value on a customer name level but I also have the order id in my view as per the requirements.
And a simple median() on quantity would give me the median value of all quantities on an order id level.
Now I know the whole thing might sound a bit confusing, so let's step back and take a look at all the elements separately.
We have 793 customers in total in our dataset. So the median quantity value should be (n+1)/2 for the customer in the middle most location which is (793+1)/2 = 397th term.
(Note : Always sort the values in ascending order. I sorted the customer name on sum(quantity) in ascending order to find the median)
Now, I want to add the Order IDs after my customer name and that will break down my level of detail to an order id level.
To avoid the median from getting calculated on an order id or customer name level, the first step would be to use the EXCLUDE function. This will calculated the median excluding the dimensions we ask it to exclude regardless of them being or not being in our view.
The next step would be to fix the median on the Customer Name level because we want to compare each median value against the overall median value of all customers.
So, our formula for #Actual Median would become : '{ EXCLUDE [Customer Name],[Order ID]: MEDIAN({FIXED [Customer Name]:SUM([Quantity])})}'
We will also make one more calculated field to check whether we have any value above our median value.
So, our formula for #Above Median would become :'IF SUM([Quantity])>=SUM([Actual Median]) THEN 1 ELSE 0 END'
To break it down, the first exclude is basically asking tableau to calculated the median regardless of what's in our view and not give it on any particular level.
The second step is asking to calculated the median on the desired level only after ignoring what's in the view.
We now get our desired median value which is 44.
Thank you for reading the blog!
You can reach out to me on any of my social media handles in case of any doubts and I would be more that happy to help.
Also, would love to get your feedbacks and suggestions in the comments below or in the DMs.