5 Replies Latest reply: Jun 16, 2017 4:35 AM by Sunny Talwar RSS

    how to calculate the sum of a calculated sum

    Xiaoyu Yang

      Hey guys,

       

      I'm searching for help which might be easy for you, but costs days for me to find out a solution...

      Original data I have like this in 2 tables.

      How Can I calculate / show the total copper weight and silver weight in each month?

      I first try to first calculate the copper and silver weight each line item by creating a new measure:

      sum(delivery quantity)*[copper weight / part]

      then I have difficulty to have a sum of each month based on this formel.

       

      I tried then the function:

      sum(Aggr(Sum([Delivery Quantity])*[copper weight / part], [Supplier],[Part]))

      But it gives me wrong figure in each month if I show them in a pivot table, however the total sum of all months is correct. Only if I select the exact month, I get the correct value. Why???

       

      Really hope I can get a quick answer.

       

      Table 1: Part master data  

      Part SupplierCopper weight / partSilver weight / part
      xA0,10,002
      yB 0,006
      zC0,40,004
      wC00

      Table 2: delivery quantity each month  

      PartSupplierMonthDelivery quantity
      xAOct1
      xANov2
      xADec1
      yBOct1
      yBNov1
      yBDec4
      zCOct2
      zCNov1
      zCDec8
      wCOct10
      wCNov12
      wCDec

      13