9 Replies Latest reply on Oct 28, 2013 8:13 AM by Jonathan Drummey

# How to plot the median of a measure, by different dimension values

I have a bar chart like the following one.  The vertical axis is a measure: duration, where the color detail indicates 3 different systems.  The horizontal axis are the different jobs being run by the systems.

I would like to plot the median duration for each system, as 3 horizontal lines. I can't figure out for the life of me how to do it.  Things I've tried:

• I can add one reference median line for the chart, but it doesn't let me separate that out by detail
• I tried to create 3 different calculated fields, one for the duration of each system.  I can create a combined chart of 2 of them, using dual axis. Then I can add reference median line for each one. But that doesn't let me plot the 3rd system.
• I tried to create a calculated field that just maps a system to its median. But I can't seem to be able to because it involved a formula that adds a condition on top of an aggregation (median).

Any help would be much appreciated!

I'm trying to plot the median of three different levels in a dimension

• ###### 1. Re: How to plot the median of a measure, by different dimension values

Hi LogicBlox,

Reorganize the position of Jobs and Systems dimensions in the Column shelf by putting first Systems and then Jobs. This way you will have a pane with all jobs by systems.

Now add a reference MEDIAN(Duration) line by pane.

I hope this helps.

If you don't get what you want, please share a package workbook with sample data of your scenario in order to provide you a solution.

Best,

Ramon

• ###### 2. Re: Re: How to plot the median of a measure, by different dimension values

Thank you Ramon. Unfortunately I really need to be able to see each system's performance per job and compare them. That's why I have Job first, System second in the columns.  Switching them means I can no longer easily see the performance of say, job 1, across 3 systems.

In Excel, I can create effectively a constant function for each job that represent the median, and plot using the constant. But I cannot seem to define such a calculated field in Tableau, because it involved a MEDIAN inside of an "if", and that made Tableau unhappy.

I've attached my workbook.  The first sheet is what I'm struggling with. Thanks!

• ###### 3. Re: How to plot the median of a measure, by different dimension values

Hi Logicblox

Thanks for sharing you package workbook.

As my knowledge, it is not possible to add a reference e.g median duration by systems in the configuration of systems per jobs/query. I'll play with you workbook to see if I can figure out a solution.

Best,

Ramon

• ###### 4. Re: Re: Re: How to plot the median of a measure, by different dimension values

Hi LogicBlox,

Take a look at attached workbook with a solution.

I created a Calculated Field " Median Duration Normalized" with formula:

```WINDOW_MEDIAN(SUM([Duration-normalized]))
```

I added the new measure "Median Duration Normalized" to Rows, and I define it Computing using QUERTY TYPE .

Right click on green pill on Rows Median Duration and click on Dual Axis, than change the mark to symbol.

I give transparency to bars to make visible the shapes. Every shape represents the median. You can use other shapes to better represent the median, let's say an horizontal line or something like that.

I hope this helps

Best

Ramon

• ###### 5. Re: How to plot the median of a measure, by different dimension values

Here's an alternative to get lines - I created three copies of Ramon's calc and put them all on the level of detail, then created a reference line for each calc:

• ###### 6. Re: How to plot the median of a measure, by different dimension values

Hi Jonathan,

Outstanding!!!

Best,

Ramon

• ###### 7. Re: How to plot the median of a measure, by different dimension values

Thank you both for your help!  That worked!

• ###### 8. Re: How to plot the median of a measure, by different dimension values

... then don't forget to mark it answered.

--Shawn

• ###### 9. Re: How to plot the median of a measure, by different dimension values

You're welcome!

On Mon, Oct 28, 2013 at 1:51 AM, logicblox.huang <