I am working with a straight table which contains a couple of dimensions and a few simple expressions. I would lile to use the total of one column ( [Sales] ) in another calculation, but doing
sum( TOTAL [Sales]) doesn't work
The [Sales] expression is a calculated field itself, not a field loaded in script.
You said Sales was not a field from the script, but the way you are using it assumes that it is.
Instead take whatever expression you are using for Sales and use that with the TOTAL parameter. We would need to see your Sales expression to give more specific information.
That's exactly what I also thought of doing, but the Sales calculation is build from other calculations that were also built upon...so I don't want to replicate the whole logic again. Some of them contain If Else statements etc.
ex:
[Sales] is [Bus Sales] + [Res Sales]
I created [Bus Sales] by adding 2 other columns: [ B Acc Value] + [B Act Value]
[B Acc Value] is created by doing If ( [B Type Value] ='record', 0, 1)
Is there an aggregation in Bus Sales and Res Sales?
You don't have to go all the way back and replicate the entire logic structure. Find the first agregation going back from Sales and then use that with TOTAL to get back to your Sales.
TOTAL will only work inside an aggregation function that's applied to a field or a calculation based on fields (but not variables that contain other functions inside).
If you have more than one field that you need to summarize, I recommend using function RANGESUM(), to avoid problems with nulls. For example:
If you have a formula like this:
sum(Revenue1 + Revenue2)
If one of the two numbers can be null() or missing, the sum will also be equal to null(). So, if Revenue2=null(), then:
Revenue1+Revenue2 = null()
, however:
RANGESUM(Revenue1, Revenue2) = Revenue1, because function RANGESUM() is replacing nulls() with zeros.
So, back to your original question about total, - the following formula, as an example, should work:
