1 2 Previous Next 16 Replies Latest reply on Mar 9, 2018 5:33 AM by Matt Spicer

    Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

    Matt Spicer

      I have a data set that is a large commodity spend data set with different sites (buying locations/Display Name),Suppliers, Commodity Types over a several year period.  I am trying to create a view that has just the top and bottom 10% of Suppliers based on Spend, Display Name, Commodity Class and a filtered time period.  In addition I have ranked the Suppliers in the top and bottom 10% from largest spend to smallest spend.  I am having a couple of issues.  One is how to show a supplier only once when it may be in the top 10% twice but with different Display Names (Purchasing Sites).  The second problem is how to get the real top and bottom 10% when my data is filtered by all the different criteria that I am using. 

       

      For the rank, I used the following formula: rank(sum({Fixed[Supplier Name],[Display Name],[Date Received],[Commodity Class Description]:SUM([Unit Qty Price Total])}))  Unit Qty Price Total is actually the Spend

       

      For the top and bottom 10% I tried: IF RANK(SUM([Unit Qty Price Total])) <= 0.1*TOTAL(COUNTD([Supplier Name])) THEN 'Top 10%'
      ELSEIF RANK(SUM([Unit Qty Price Total])) > TOTAL(COUNTD([Supplier Name]))-(0.1*TOTAL(COUNTD([Supplier Name])))
      THEN 'Bottom 10%'
      ELSE 'Hide'
      END

      The formula top and bottom 10% is not working properly and I knew it wouldn't but nothing I tried seemed to work.  Does anyone know how I can correct my formula or a more efficient way to accomplish this?

        1 2 Previous Next