Loading

Creating a Decile of [Measure] Dimension

Publiseringsdato: Apr 13, 2024
Beskrivelse
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.
Løsning
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.
Knowledge-artikkelnummer

001498367

Vedlegg

Decile Bins.twbx

1198 KB

 
Laster
Salesforce Help | Article