1 Reply Latest reply on Sep 20, 2015 4:07 PM by Bill Lyons

    Taking Sum and Percentages of Amounts for Duplicated Ids

    Pratik Gandhi

      I have got two tables - one with Stage, Id and Amount fields and other with product details. Each Id belongs to any one of the stages. Now, I need to take percentages of total amounts for all Ids categorized by their stages. So consider this

       

      StageIdAmount
      A110
      B220
      A330
      C440
      C550
      A660
      A770
      B8

      80

       

       

       

       

       

       

       

       

       

       

       

       

      I need to take percentages of nos. in bold and then add them up.

      Stage IdsAmt.
      A110
      330
      660
      770
      170
      B220
      880
      100
      C440
      550
      90

       

      I can get this to work if this table is all by itself; the moment I join (left) my product table, ids get duplicated and I get an inflated figure.

      I tried using FIXED function and getting a max amt for an Id, but that doesn't seem to work when I want to categorize and filter it by stages. Mind you, I just need the final sum of those percentages and not the details as shown below.

      StageIdProductAmount
      A1aaa10
      A1mmm10
      B2bbb20
      A3ppp30
      C4ccc40
      C4qqq40
      C5ccc50
      A6aaa60
      A7bbb70
      B8mmm80
      B8qqq80
      Stage IdsProductAmt.
      A1aaa10
      1mmm10
      3ppp30
      6aaa60
      7bbb70
      180
      B2bbb20
      8mmm80
      8qqq80
      180
      C4ccc40
      4qqq40
      5ccc50
      130

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      I don't know if its something simple and I'm missing anything or is it really that complicated as it's turning out to be.