5 Replies Latest reply: Oct 17, 2014 6:43 AM by yuntao peng RSS

    Caculation of Total

    yuntao peng

      Hello everyone, I am encountering a calculation problem:

       

      Here is a simplified table:

      itemvalueSource
      190B
      1100A
      250B
      360C

       

      The calculation rule is as follows:

      for an Item that can be found in the source A(<>0), we should take the value from source A, otherwise we take the value from Source B.

      If we find values from both source A(<>0) and B(<>0), we still take the value from source A.

      The same logic applies where there are more sources.

       

      How can we get the right total by using a formula? In the above example, the total should be 100+50+60=210?

       

      Here's the formula I used but I don't get the right answer.

       

      =if(sum({$<Source={'A'}>}[value])<>0,

      sum({$<Source{'A'}>}[value]),

      if(sum({$<Source={'B'}>}[value])<>0,

      sum({$<Source={'B'}>}[value]),

      if(sum({$<Source={'C'}>}[value])<>0,

      sum({$<Source={'C'}>}[value])

      )))

       

      Thanks for your help!