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

# Calculate two values - one being summed the other distinct

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!

• ###### Re: Calculate two values - one being summed the other distinct

sum(amount * rate)

• ###### Re: Calculate two values - one being summed the other distinct

I've tried it that way but it comes up with a wrong number even at the lowest level of the drill down.  So I'm not sure what it is doing behind the scenes to get the wrong number.

Here is an example of doing it that way and what I get.

CC          Amt          Rate               Calced value

301          8180.92     7.03852          14395.3922

• ###### Re: Calculate two values - one being summed the other distinct

Actually now that I look at the data, I think it is because I have a date issue because I'm trying to do this at a month level but forgot that it is loaded at a week level so my numbers are not correct for the rate.  So I think that may be why I think I'm off but the calculation is actually correct.  Thanks for the help as that made me review it and see the data!