I would like to calculate the sum of the difference between field1 and field 2 by each row in the pivot table, then get the absolute value of the sum, then calculate the proportion of each absolute value.
I used this expression, which did not work: FABS(sum(field1) -sum(field2))/sum(total FABS(sum(field1) -sum(field2))).
Then I changed it to FABS(sum(field1) -sum(field2))/sum(total aggr(fabs(sum(field1)-sum(field2)), dim1)), which worked if I have a fixed row dimension.
But I have dynamic row dimension, the row dimension changed based on a filter pane. So, 'dim1' in the expression is not necessarily dim1, it can be dim2 if users want. If I change 'dim1' to if() expression, the total sum function did not work. It only worked if dim1 is a field name. Is there any solution for this problem? Or is there any other ways to calculating the sum total in my case?
