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!
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)