I have a volume variance calculation which is calculating at the lowest level of detail for my report (PartNumber and StatementAccountNumber) combination. The issue that I have is related to when you want to total a column on a pivot table, in that I need a total to be returned which is the sum of the lowest level of detail NOT the expression working over the totals of the detail. I believe that to resolve this you use the following formula:
Sum(AGGR( Formula ),Dim1,Dim2))
For me this formula looks as below:
Sum (AGGR(
//If Volume movement is zero (allows the manual postings to calculate)
(If(((Sum({$<Scenario = {Actual}>}ValueInKG)/1000-sum(ValueInKG)/1000)=0),
//0 - all rate
0,
//If scenario volume is 0
(if(sum(ValueInKG) = 0,
//Then Net Revenue Actual
sum({$< Scenario={Actual}>}NetRevenue),
//Else Net Rev/T (Scenario) * Volume Movement (Actual - Scenario)
(sum(NetRevenue)/(sum(ValueInKG)/1000))* ((Sum({$<Scenario = {Actual}>}ValueInKG)/1000)-(sum(ValueInKG)/1000))))))
,StatementAccountNumber,PartNumber))
If I use this formula then it calculates perfectly for some items but returns zero for others (If I look at what is calculating at the lowest level) hence it returns an incorrect total. I believe that this is related to the "Scenario" field in my formula (three scenarios exist - Actual is the main comparitor versus the other scenarios of Budget and Last Year). The formula will calculate correctly for items where the scenario that has been selected contains data (i.e. budget or last year), however, if it doesn't contain data, but the same PartNumberStatementAccount combination has data for the Actual scenario it returns blank (instead of the volume variance).
Does the AGGR field need a scenario setting for it?
