Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Use profitability percentile as a variable to rank customers - problem with dimensions

I'm trying to rank (A,B,C) a list of customers according to their profitability , which is calculated as the amount of each sale multiplied by the product profitability (each product has a profitability value assigned). Hence,  Profit = SaleAmount*ProductProfitability

To rank every customer, I have a pivot table with the customer id (CustID) as dimension and two expressions:

1) = SaleAmount*ProductProfitability

2) = if(SaleAmount*ProductProfitability > \$(vPercentile75Profit),'A',

if(SaleAmount*ProductProfitability > \$(vPercentil25Profit),'B','C'))

Expression 2) works correctly if I fix the values of vPercentile75Profit and vPercentile25Profit, but obviously I need this to be dynamic.

For that I've defined those variables as (same for both, just switching 0.75 with 0.25):

vPercentile75Profit =Fractile(aggr(sum({\$<ProductProfitability = {'>0'} >}  SaleAmount*ProductProfitability/100),CustID), 0.75)

If I understand well, this calculates a list of each customer profitability and then performs the 75 percentile of that list (which is a single value). This works great if I show the value in a Text box for example, however, if I use it in my table, it takes a different percentile for each customer (since CustID is in the dimension).

How can I bypass this? The percentiles must be the same for each customer, but I cannot find the way.

Thanks in advance, any help will be greatly appreciated!

1 Solution

Accepted Solutions
Not applicable
Author

Nothing works better to find the answer than asking your question to others... It was as simple as adding TOTAL to the variable definition:

`vPercentile75Profit =Fractile(TOTAL aggr(sum({\$<ProductProfitability = {'>0'} >} SaleAmount*ProductProfitability/100),CustID), 0.75)`

`vPercentile75Profit =Fractile(TOTAL aggr(sum({\$<ProductProfitability = {'>0'} >} SaleAmount*ProductProfitability/100),CustID), 0.75)`