Totals on complex formulas in pivot table

[#1]

Hi,

we have a problem to calculate correct totals in pivot table. It works fine for simple sum() statement but when it comes to complex calculation it displays (-).

First we had an complex If then construct - sums wasn't working.

e.g.:

if (dimension = 'X', sum(sqrt(Value), sum(sqrt(Value) - Value))

Second we developed a methode to sum the different sums an mulitply each with a factor of 0 or 1.

(sum(sqrt(Value))* Factor1) + (sum(sqrt(Value) - Value) * Factor2)

Anyhow we can not calculate the correct totals. i have read about the arrg() function, but surrounding this expression doesnt worked. Any Ideas?

Greeds Boris

[prieper · Accepted solution]

Hi Boris,
in an expression you should use the IF within the Aggregation-function, like:

SUM(IF(dimension = 'X', SQRT(Value), SQRT(Value) - Value))

HTH
Peter

[#3]

Ok i will give it a try