3 Replies Latest reply: Jul 15, 2014 5:41 PM by Karla Youngdahl RSS

    Calculate two values - one being summed the other distinct

    Karla Youngdahl


      I have a calculation that I need to perform that has two values - a summed amt over a drill-down group of a mgmt structure * a rate that can not be summed but needs to be calced at the lowest level.  But when I try to write a formula of sum(amt) * sum(rate) it works at the lowest level of the Group that is a drill-down group.  But when I drill up on the group, the calculation no longer works because it is summing the rate where I need it to actually do the calculation at the lowest level and then sum.  Help??

       

      For example:

       

      I have a Mgmt Grp 5 of Production which has 3 Cost Centers - 301, 302, 303.  I've created a Group called Mgmt that has the Mgmt Grp 5 and then Cost Center.  The data for the Cost Center is:

       

      Cost Center     Amt          Rate     calculated expression at lowest level Amt * Rate

      301                200           3.2          640

      302                400           7.1          2840

      303                500           3.3          1650

       

      so rolled up at the top level for this Mgmt Grp 5 would be a value of 5130 but instead I get 1100 * 13.6 = 14960.  I know this is because I'm using the sum function but not sure what else to use to make this work correctly.  Any help would be appreciated.  Thanks!