Hello @ray985,
I have this same issue and reached their Support. Since we can't pull the Aggregate Function and enter it in the footer, what they suggest is to create another calculated field to get the aggregated/SUM value of a column. And yes, the above statement is correct, that we can use SQL. And by then, you can use [@calcfield:X] in the footer, since it is available from the picker.
— DTX —