Hi all,
Second query that is a bit mind-boggling unless the answer is staring me in the face
scenario:
4 columns:
Col A = Actuals calc - case statement when column z is x then y+x else y)
Col B = Budget - but it's case statement for budget. So when Department = A then 10,000, when B then 20,000
Col C = Variance calc (col a - b)
Col D = Variance % (col b / c and then set to percent)
There is also a subtotal row which does a total on Col A, Col B, Col C and it seems like Col D
A,b,c and D work fine
when it comes to D, it is not calculating calculating the subtotal correctly - instead of doing the total(col b) / (total(col c), it seems to be doing a total on Col D which I suppose is expected given the others are meant to do that on the basis I wanted a total row.
The issue is I can't get it to divide at all - trying all sorts of aggregation options and also unlocking the report, and replacing that single column total to a total B / total C.
I've also added total b for report / total c for report but still no go
it just carries on doing a total of the col d going down rather than taking a total for b and a total of c and then dividing it.
I'm sure it's staring me in the face but it's driving me round the bend
thanks in advance
Quote from: c8aj on 25 Apr 2016 05:06:01 AM
Hi all,
Second query that is a bit mind-boggling unless the answer is staring me in the face
scenario:
4 columns:
Col A = Actuals calc - case statement when column z is x then y+x else y)
Col B = Budget - but it's case statement for budget. So when Department = A then 10,000, when B then 20,000
Col C = Variance calc (col a - b)
Col D = Variance % (col b / c and then set to percent)
There is also a subtotal row which does a total on Col A, Col B, Col C and it seems like Col D
A,b,c and D work fine
when it comes to D, it is not calculating calculating the subtotal correctly - instead of doing the total(col b) / (total(col c), it seems to be doing a total on Col D which I suppose is expected given the others are meant to do that on the basis I wanted a total row.
The issue is I can't get it to divide at all - trying all sorts of aggregation options and also unlocking the report, and replacing that single column total to a total B / total C.
I've also added total b for report / total c for report but still no go
it just carries on doing a total of the col d going down rather than taking a total for b and a total of c and then dividing it.
I'm sure it's staring me in the face but it's driving me round the bend
thanks in advance
What happens if you change the aggregation from 'Total' to 'Calculated'? Does this give you the result you need?
MF.
Nope - still the same. Totals the column to give it a silly amount
Did you get this fixed?