5 Replies Latest reply on Jul 19, 2016 3:09 PM by Yuriy Fal

# Reference line median does not match median aggregate calculation

I have a worksheet that's a boxplot of some durations.  It's constructed by adding a reference line -> boxplot plot and reference line -> distribution (quartiles).

However, the middle line of the boxplot and the 2nd quartile (i.e. 50th percentile) produce a different median value than if I were to add a measure of median(duration).   The aggregate calculation median(duration) is only available with an extract, while the reference line boxplot and distributions don't require it.  I've also noticed that the reference line type median follows the quartiles and boxplot, and therefore also doesn't match the calculated aggregate median(duration).

Why in the world are there different medians?  How can I get a boxplot displayed with the aggregate median(duration) not the messed up one that the reference line computes?

• ###### 1. Re: Reference line median does not match median aggregate calculation

It's difficult to investigate without your data. Could you add your packaged workbook?

Anyway, you can start checking the scope first..

Thanks,

Shin

• ###### 2. Re: Reference line median does not match median aggregate calculation

Here's a stripped down workbook demonstrating the problem.

The top part of the pane uses the Median() calculation, the bottom portion uses reference lines, and they show different values despite the same data source.

• ###### 3. Re: Reference line median does not match median aggregate calculation

After some more digging, I've found a few threads that shed some light on this.

First, Tableau uses different calculations for creating a median depending on whether it's an aggregate calculation or a reference line.

Second, this thread shows how to create reference lines from a calculated field that relies on the window_percentile function that works the same way as the median aggregation (e.g. "IF FIRST() == 0 THEN window_percentile(ATTR([Time to termination]),0.50) END").

But that was still giving me crazy answers until I found this question (more particularly the response by Jim Wahl) which suggests turning off aggregate measures (go to the Analysis menu and uncheck Aggregate Measures).

But more interestingly, turning off aggregate measures seems to solve the problem without the need for calculated fields.  When I turn off aggregate measures, the default reference line's median calculation suddenly changes to match the Median() aggregate....

I'm glad I can now use the calculation I need, but why does turning off aggregate measures change how the median is calculated?  What other effects might it have?

• ###### 4. Re: Reference line median does not match median aggregate calculation

I tried to understand why... in other word, tried to create number 501 and 676.

Then here is a calculation logic to generate both numbers.

Hope this can be some hints.

Using ID as unique line item.

Using "Time to Termination" as Dimension

Thanks,

Shin

• ###### 5. Re: Reference line median does not match median aggregate calculation

Hi all,

Shin is right with his investigation.

MEDIAN() aggregation is for the whole dataset

with the row-level granularity (defined by [id] in this case).

Median Ref Line is a Table Calculation

with the granularity defined by all Dimensions

on a view -- [Time to termination] in this case.

If one put the [id] field to the Details shelf

of the [Time to termination] Green Pill (on Rows),

the view granularity for both Green Pills on Rows

would be the same, hence 501 on both.

Yours,

Yuri