1 Reply Latest reply: Jun 30, 2017 6:43 AM by Carlos Borau RSS

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

    Carlos Borau

      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!