2 of 2 people found this helpful
You have to do a running sum and calculate % of total. Once you have that calc field, you can then create another calc to define A, B, and C.
RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales]))
if [Run Sum Total] < .8 then "A"
elseif [Run Sum Total] < .95 then "B"
Hope this helps.
1 of 1 people found this helpful
One other way to do using a single calculated field is using percentile option.
if RANK_PERCENTILE(SUM([Sales])) > .2
ELSEIF (RANK_PERCENTILE(SUM([Sales])) < .2 and
RANK_PERCENTILE(SUM([Sales])) >= .05 )
Note :- You would need to format your sales field in the rows to Number(Custom) with Decimal places 2
Good Luck !!!
I tried what you said but I am facing the same issue. If I sort on Customer Name, the value changes. It should not be dependent on load order or any other sort category and just be dependent on absolute sales. Can you please see take a look at the workbook attached ?
CustomerCategory.twbx 14.4 KB
Thank you for the reply Anoop. I am guessing Rank function is way to go. I tried using your function but still not getting the desired result. Please see attached. Any suggestion?
CustomerCategory.twbx 15.6 KB
Anoop, thanks for the solution, I feel I should explain in slightly more detail. If you see the below image, C1 and C5 make up 80% and should be in category A , C3 in B and C2 in C.Basically rank the customers by sales, calculate running total percentage and club those making up to 80% in Bucket A and so on.. Hope this helps
That's it. LOD to the rescue. Thank you so much