Pivot Tables Aggregation

Hi,

I would like to create a Pivot Table where I have say four rows all in column format.

They are Broker, Portfolio, Port ID, Rate(decimal).

The data is Amounts in dollars.

You have many variations across these four fields but for illustration and more particular to address my problem here is an example:

Broker is A, Portfolio is B, Port Id is 100 and Rate is .45.

I have set the table up such that it only gives me the Total for Broker.

Carrying on with the example:

There are 13 records of  A,( each worth 1000), they all have Portfolio B and Port ID of 100 as well as a Rate of .45

The total for Broker in this example is 13000.

I create a calculated field and it reads...due Broker=amount*rate

and

I get: (.45*13)*13000=76050 (it is 13 times too big)

when

I am expecting .45*13000=5850.

Is there a way to set up the pivot table such that I can have it do the latter calculation or is it better to just create columns in the underlying data and do the calculation there?

Thanks for the many lessons I am learning here.

Hello Fuzzy, (I think I've actually said that once in Thailand)

Calculation formulas cannot reference a pivot table’s Sub Totals or Grand Total. This means that you can't use the result of a Sub Total or Grand Total as a variable or argument in your calculated field.

Calculated field calculations are always performed against the sum of your data. In basic terms, Excel always calculates Data Fields, Sub Totals and Grand Totals before evaluating your calculated field. This means that your calculated field is always applied to the sum of the underlying data.

What's happening is that your Sub Total is calculating the sum of number of units * the sum of price which returns the wrong answer.

Unfortunately, there is no solution to this problem.  Your workaround (creating columns to the underlying data) is as good as any.