Calculated dimension to group customers into classes

[#1]

I have a list of hundreds of customers and their sales amounts. I would like to create a calculated dimensions to group these customers into e.g. 5 classes (class 1: the 20% customers with the lowest sales, class 1 : the bottom 20% customers,..., class 5: the top 20% customers) so I can display total sales and other information for each of these classes.

Thanks for the help

[#2]

It depends upon your definition of "20%".

1 - Consider the higher spending customer, fix it's spending power to 100% and divide by 5

or

2 - Pareto Analysis: sort the customers by spending power, sum up the higher spending ones until you obtain the 20% of total incomes and assign to them an A, repeat for B, C, D and E

In case 1 you can assign a variable the value "=Max(Aggr(Sales, CustomerId))", then use a calculated dimension to compare with this value (with ifs, match or whatever you want).

In case 2 you must use a macro using the ParetoSelect command.

See here for another thread on the argument.

Hope this helps!

[#3]

The second scenario is what I am looking for (i.e. grouping the customers into classes A, B, C, D, and E, with the top 20% customers withe the highest sales in class A etc.). The macro is beyond my expertise. Isn't there a more straightforward way?

I already figured out how to sum the sales of e.g. the top 20 % customers as follows SUM( IF(Sales>Fractile(Total Sales, 0.8), Sales,0)). Isn't it possible to build on this to select from the field Customer the subset of customers who meet the IF criterium as used in the above SUM expression?

[#4]

Please note that the fractile will not aggregate values by customer, so you have to provide a single "sales" value for each customer.

In my situation I had a sales table with many records for each customer, so I could not use the fractile function.

Mmmm... try something like:

pick(1+ If(Sales>Fractile(Total Sales, 0.8), 1,0) + If(Sales>Fractile(Total Sales, 0.6), 1,0) + If(Sales>Fractile(Total Sales, 0.4), 1,0) + If(Sales>Fractile(Total Sales, 0.2), 1,0), "E", "D", "C", "B", "A")

(I just wrote this down, so it's not tested, but it should work)

The idea is to provide the pick function with a value between 1 and 5. You could write it with nested ifs too and it would be a little faster, but I present this version for the sake of simplicity.