9 Replies Latest reply: Aug 30, 2013 1:32 AM by Prashant Adhav

# Incorrect Subtotals in Pivot Table

I have two tables

Table A

Country

Model

Table B

Model ( Few Models )

Cost

When I Left Join Table A with B  for some Model which are not in table B Cost is
null. And when I use Pivot table to calculate count(Model)*Cost
and try to get subtotals I get 0.

Table A                                 Tableb

Country  Model                Model                   Cost

A                           a                           10

A

b                            e                          14

ABC           e                             f                           12

e

F

XYZ           a

b

Can you please help me to get proper subtotals.

• ###### Re: Incorrect Subtotals in Pivot Table

Hi,

Use RangeSum(count(Model) * Cost)

Hope it helps

Celambarasan

• ###### Re: Incorrect Subtotals in Pivot Table

Hii Nisha,

use the following expression.

count(Model)* if(isnull(Cost),0,Cost)

This will consider the null values as 0.

-Nilesh

• ###### Re: Re: Incorrect Subtotals in Pivot Table

Thanks Nilesh, I have already tried this  but not getting the desired result.

Please find the result which I get in the Pivot table.

For some country I get the subtotal which is random and for tother even the subtotal is missing.

@Thanks,

Nisha

• ###### Re: Incorrect Subtotals in Pivot Table

Can you attach the sample app?

• ###### Re: Incorrect Subtotals in Pivot Table

try this

count(Model) * if(Cost=' ',0,Cost)

• ###### Re: Incorrect Subtotals in Pivot Table

Hello Vishwaranjan,

Thanks. Its giving me correct total but the partial subtotals are still 0.00 and for some its random number.

• ###### Re: Re: Incorrect Subtotals in Pivot Table

Try this:

sum(

aggr(

sum( alt( cost,  0 ) )

,

Model

)

)

• ###### Re: Incorrect Subtotals in Pivot Table

Doesnt help much.

• ###### Re: Incorrect Subtotals in Pivot Table

use aggr function by declaring the field u want the aggregation for.... tht will solve the problem