I've got a Pivot Table with an expression that can produce negative values. The negative values are displayed correctly. The totals however seem to stop working whenever there is a negative value to include, all I get is "-". Totals over only positive values (including 0) work fine.
The expression is something like = Count(x) * y, where y can be 1, 0 or -1.
What am I doing wrong here?
Edit - just tested this with another expression: = Sum(z), where z can be different values (negative as well as positive). And behold: The totals work! Will do some more testing...
Second edit - substituted the first expression = Count(x) * y with = Sum(y) which produces the same result plus working totals (and is more elegant even). But I still think the first expression should have worked also.
I suspect the problem is that the pivot table is applying your expression again for the total, but at that point, Y has multiple values, so it can't do the multiplication, so it returns null, displayed as '-'.
Sum(Y) works because it puts Y INSIDE of the aggregation, and thus each ROW only has one value, so it can do the calculation.
If that wasn't an option, you could have used dimensionality() to detect whether you're on that particular subtotal, and substitute the expression you want. Something along these lines for a one-dimensional pivot table with a grand total:
