PivotTable (Dynamic Table) subtotal is wrong

[luiz_damascena]

I have a dynamic table with three dimensions (country, state, city) and three expressions (sales, deliveries, pending).

The expression "pending" is a formula: if (deliveries> sales, 0, Ceil ((sales - deliveries), 1))

By hypothesis, three lines;

in the first, pending = 10;

in the second, pending = 0 (deliveries are greater than sales, without the formula, the result would be -5);

in the third, pending = 20.

Subtotal display is enabled; the sum should be 30 (10 + 0 + 20). But he checks 25 (10 - 5 + 20). Why?

I tried some combinations using AGGR in the formula, but I did not succeed.

Thanks in advance.

LC

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(Expression4DeliveriesHere > Expression4SalesHere, 0, Ceil((Expression4SalesHere- Expression4DeliveriesHere), 1)), country, state, city))

[hic]

First of all, the expression

if (deliveries> sales, 0, Ceil ((sales - deliveries), 1))

contains several naked field references, and these can often not be used in your measures. You need to wrap all field references in an aggregation function like Sum(). I.e. you need to decide whether to aggregate first, and then use the condition in the If(), or if you want to use the condition in the If() on each data record, and then aggregate. See more on Use Aggregation Functions!

Further, the subtotal is not necessarily the sum of rows - and it shouldn't be. See Totals in Charts

Finally, if you choose a solution with Aggr(), the structure should be

Sum(Aggr(Sum(...), ... ))

Note that you should have one aggregation function outside the Aggr() and a second inside the Aggr().

HIC

[luiz_damascena]

It worked perfectly!

Not being able to use the names of the expressions, and having to repeat all the content of the same ones in the formula, is annoying ...

But thanks, the solution worked perfectly.

[luiz_damascena]

Thanks! the links are of excellent material.

[sunny_talwar]

You can use variables if you would not like to repeat the expressions over and over again, but column labels don't really work within Aggr() function.