Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
what about
sum(amount * rate)
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
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!