I need to do a fractile function of sales by fiscal year and representatives in the load editor. I need 5 different segements for each representative that has a given sale for each date. I need the lowest sales to be in the first 20% with a score of 5, the next 20% with a score of 4 and so on. I also need this to be done by each fiscal year not just placing quartile on all sales. Placing quartiles on the sales of fiscal year 2022, fiscal year 2023 and so on.
I have a sales table with the field sales, representative and date. I also have a master calendar table in which the sales table is linked to by date fields. The master calendar contains the Fiscal Year field.
I did attempt to solve this problem by creating a measure, however, once I use a filter on the sheet the output does not remain the same. I need the output to remain the same, this is why I want it done in the load editor. Here is the formula I wrote as a measure.
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.20), 5,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.40), 4,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.60), 3,
if(aggr(Sum(sales),Rep) <= fractile(TOTAL aggr(Sum(sales),Rep), 0.80), 2, 1))))
