10 Replies Latest reply on Jul 12, 2016 2:30 PM by Jonathan Drummey

How to calculate a trim mean for a reference line?

hello all,

I would like to insert a reference line in the chart for a trim mean, how does one go about formulating the trim mean calculation on tableau?

thanks.

• 1. Re: How to calculate a trim mean for a reference line?

You would need to create a calculation just like any other. It would involve window calculations as you would need to know what was to be trimmed. Do you have a formula you would like to use?

• 2. Re: How to calculate a trim mean for a reference line?

Take a look at this.  I think this is correct, but please check the calculation.

• 3. Re: How to calculate a trim mean for a reference line?

Realized a lower parameter control would be useful as well   Updated.

• 4. Re: How to calculate a trim mean for a reference line?

Hello Ben, thanks for this. this method for trim mean works.

however when i right-clicked on my chart to view the data, the percentiles i got were up to 33% only. what did i miss?

• 5. Re: How to calculate a trim mean for a reference line?

Hi Ben,

Thanks for posting this one. In the calculated field "Percentile", what is the purpose of subtracting .05 from INDEX()?

Percentile

(INDEX() -.05)/Total(Count([OrderID]))

--Tim

• 6. Re: How to calculate a trim mean for a reference line?

Tim,

Great question, maybe you can help answer it.  I was using reference material from Stanford

http://www.stanford.edu/class/archive/anthsci/anthsci192/anthsci192.1064/handouts/calculating%20percentiles.pdf

Wikepedia has same equation.

http://en.wikipedia.org/wiki/Percentile

I sold my stats textbooks many many many moons ago.  Can anyone help answer?

• 7. Re: How to calculate a trim mean for a reference line?

Hi Ey,

The reason that it's not calculating correctly is that you don't have a unique id for the sale; I added a concatenated field Month + Outlet and it calculated properly.  Also the trimmed mean you have on the monthly view is not calculating properly, it should go down when you lower the upper threshold (eliminating high range from median calculation).  Why it's doing that is beyond me right now.

• 8. Re: How to calculate a trim mean for a reference line?

Hi Ben,

Thanks for posting a link to those papers. I see the calc in the Stanford paper and will dig deeper this weekend. It will give me a little light reading for the weekend.

--Tim

• 9. Re: How to calculate a trim mean for a reference line?

hi Ben, I created a dummyID and my chart turns out weird. I've edited the formula to percentile involving the dummyID at the denominator but still my percentile range is not up to 99%.

Regarding the adjustment of the upper threshold resulting in higher trim mean, most likely it's because the data is made to display in descending order and the upper tails are the lower values.

• 10. Re: How to calculate a trim mean for a reference line?

A few years later, here's a version of TRIMMEAN() in Tableau that uses Level of Detail expressions:

TRIMMEAN() in Tableau | Drawing with Numbers

Jonathan