3 Replies Latest reply on May 17, 2016 11:32 AM by Shayna Mullins

# How to Create Calculated Field which separates values above 80th percentile with the rest.

Simply put, the calculated field will allow me to filter values based on whether they are above the 80th percentile or lower. So only two possible categories/values exist for this field: "Above 80th Percentile" or "Below 80th Percentile".

Context
The data I have are sum insured values of homes (eg, \$100,000; \$130,000 etc). The distribution looks like this:

I want to be able to remove the outliers easily no matter what dimension I add to this data set.

I am thinking that I would like to create a calculated field where the values are either "Above 20%" or "Below 80%".

The formula I have chosen is this:

IF ATTR([Sum Insured]) < PERCENTILE([Sum Insured], 0.8) THEN "Below 80%" ELSE "Above 20%" END]

(note I used the ATTR function on the [Sum Insured] measure as Tableau would not let me use disaggregated and aggregated data on the same function.

Now this formula was syntactically correct but (1) it doesnt allow me to treat it as a Dimension and (2) when I add it to the Columns area of the graph, it doesnt do anything.
Does anyone know what I am doing wrong?

• ###### 1. Re: How to Create Calculated Field which separates values above 80th percentile with the rest.

Hey Gil,

Your calculated field seems correct so I'm not entirely sure what the issue is. Have you made any progress on this? If not, a packaged workbook would be super helpful.

Thanks,

Diego

• ###### 2. Re: How to Create Calculated Field which separates values above 80th percentile with the rest.

Thanks Diego

I didn't end up getting a solution to this particular method. But I did eventually find an alternative method using the percentile table calculation and then filtering off the top 20%.

Cheers

Gil

• ###### 3. Re: How to Create Calculated Field which separates values above 80th percentile with the rest.

You should get you what you want using LOD expressions. (Not sure if those were available when you posted this question.)

IF SUM( { FIXED [Home ID] : SUM( [Sum Insured] )}) <= TOTAL( SUM( [Sum Insured] )) * .8

THEN "Top 80%"

ELSE "Bottom 20%"

END