KNOWLEDGE BASE

Creating a Decile of [Measure] Dimension


Published: 10 Jan 2017
Last Modified Date: 06 Jun 2017

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.

Environment

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 "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .4) } THEN "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .5) } THEN "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .6) } THEN "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .7) } THEN "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .8) } THEN "20%"
    ELSEIF{ FIXED [Customer Name] : SUM( [Sales] ) } <= {PERCENTILE({ FIXED [Customer Name] :SUM([Sales]) }, .9) } THEN "20%"
    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?