1 Reply Latest reply on Jan 23, 2017 8:36 AM by Stephen Rizzo

    Creating categorization based on ranked aggregate values

    Rory Liu

      Hi,

       

      I'm working with a set of transaction data and am trying to do a classic ABC analysis. Basically, I'm trying to group products into ABC categories where A products accounts for 80% of revenue, B for 15%, and C for 5%. I managed to do this based on this post: Binning aggregates by a table calc

       

      However, after creating this categorization, I'm also hoping to do further analysis with ABC as a cut. For example, I want find out in each of the A,B,C categories, how are margin distributed (e.g. how many products have margin >20%, how many has margin <5%, etc.) In order to do this, I used { FIXED [Product ID]:(sum([Net Price Revenue])-sum([Costs]))/sum([Net Price Revenue])} to find average margin per product, and then created bins to group them into margin groups. However, if I do a chart with ABC categorization on the x axis, # of product IDs on the y axis, and then drag this margin group into color, it messes up the ABC categorization completely..

       

      Is there a way to achieve this?

       

      Many thanks in advance!