KNOWLEDGE BASE

## Creating a Decile of [Measure] Dimension

Published: 10 Jan 2017
Last Modified Date: 09 Mar 2018

### Issue

Unable to generate a dimension that will sort each record into its decile (<10%, 11-20%, 21-30%, etc) and use that decile as a dimension on the view.

Tableau Desktop

### Resolution

The above steps can be reviewed in the attached workbook "Decile Bins".

### Option 1

An IF/ELSEIF calculation can be combined with LODs and the PERCENTILE aggregation. For example, deciles of sales on the individual record/underlying row level looks like this:
1. Select Analysis > Create Calculated Field...
2. Name the calculated field, enter the following formula and click OK
```IF [Sales] <= {PERCENTILE([Sales], .1)} THEN "<10%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .2)} THEN "20%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .3)} THEN "30%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .4)} THEN "40%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .5)} THEN "50%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .6)} THEN "60%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .7)} THEN "70%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .8)} THEN "80%"
ELSEIF [Sales] <= {PERCENTILE([Sales], .9)} THEN "90%"
ELSE "100%"
END```
Note that the above formula will evaluate individual rows/records for their decile position.

### Option 2

If you are looking to compare an aggregate, some modifications will need to be made. For example, if you want to evaluate each customer's decile of sales, the formula will need to look more like this:
1. Select Analysis > Create Calculated Field...
2. Name the calculated field, enter the following formula and click OK
```IF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .1) } THEN "<10%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .2) } THEN "20%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .3) } THEN "30%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .4) } THEN "40%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .5) } THEN "50%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .6) } THEN "60%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .7) } THEN "70%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .8) } THEN "80%"
ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .9) } THEN "90%"
ELSE "100%"
END```
Note that the State pill on the Filters card is gray. This is to denote that it has been added to the context of the view. This is important as FIXED Level of Detail expressions are not affected by standard blue or green filters; only context filters are processed before the Level of Detail expression is evaluated.
Did this article resolve the issue?