sum total function did not work in pivot table

[xingstar97]

I'm wondering why the sum total function in the pivot table didn't work, and how I should revise it?

FABS(sum(field1) -sum(field2))/sum(total FABS(sum(field1) -sum(field2)))

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.

[LRuCelver · Accepted solution]

Try this:

fAbs(Sum(Field1) - Sum(Field2)) / Sum(total <Dim1> Aggr(fAbs(Sum(Field1) - Sum(Field2)), Dim1, Dim2))

Dim1 and Dim2 are the dimensions you are using in the pivot table.

[xingstar97]

Thanks for your reply! I have a follow up question. The dimension of my pivot table is not a specific field, it depends on a filter pane. That is, users can choose the dimension of my pivot table, if they choose 'field1', the dimension is field1. Therefore, I can't specify dimension in your expression. Is there a way to make it work in my case?

[LRuCelver]

May I ask how exactly you have implemented this?

But replacing the field name with whatever expression you have used to get the field as a dimension in the table should in theory work.

[xingstar97]

I use the following expression: 

if(Match(GetFieldSelections(filter3), 'field1'), field1, ) & if(Match(GetFieldSelections(filter3), 'field2'), field2, )

I tried to replace 'dim1, dim2' in your expression with the expression I provided above, but it didn't work. I learned that aggr() can only accept single field as the dimension, not any expression. Not sure if this is the reason. Any suggestions would be appreciated!

[LRuCelver]

You expression only works if there is exactly one selection in the "filter3" field and when selecting the value in the chart, the filter is not passed through to the field1/2 fields, correct?

There are a few ways you can improve this:

• An alternative to using a field as the method of selecting the dimension are variables. You can create e.g. a dropdown where users can select the dimension they want to use. You can use this expression as the dimension:

  [$(vFieldSelectVariableName)]

  Filters applied to the dimension in the chart will be applied to the correct field.
• If you are using a field to select the dimension, you can enable the "Always one selected value" option in the field settings. Similar to the option with the variables, you can access and apply filters to the selected field like this:

  [$(=YourFilterField)]

If you have no, an invalid or multiple values selected, both options can and likely will result in errors. Make sure to add calculation conditions to catch these errors.
Both options can be used in any expression like you would use a normal field name.