Calculated Fields in Pivot Tables

[Philipsfn]

Hello,

I am trying to insert a calculated field in a pivot table but for some reason its showing the wrong totals. The formula I am using is Quantity * Price

the only caveat is the price is different for each customer. Would this throw the totals off?

in the attached file I am looking for the calculated field to show total contract sales of $3,672.49 but its showing $87,637.42

Thanks,

[Richard Buttrey]

Rather than the calculated field use the total value fields from the data.

See attached

[Philipsfn]

Thanks Richard. I guess there is no way to do this within the pivot itself?

[Richard Buttrey]

Thanks Richard. I guess there is no way to do this within the pivot itself?

You can do this with a calculated field

Please Login or Register  to view this content.

Then in the Sum of Calculated Field Values choose Running Totals in Product ID

[Philipsfn]

Thanks Richard. I tried that but it wont display the total in the bottom. Please see attached. Contract Sales 2 column. Thanks

[romperstomper]

You need to load the data into the data model so that you can create the measure you need. In a normal pivot table, the formula equates to =SUM(Quantity)*SUM(Price) and you can't alter that.

[Philipsfn]

Thanks for your response. Yes, I get that by doing the calculation in a separate column in the data I can bring that into the pivot but I was wondering as to why the calculated filed does not sum correctly?

[romperstomper]

It does sum correctly, just not the way you want. Calculated fields always operate on the aggregated totals of the fields involved.

[Philipsfn]

Great. Thanks

[romperstomper]

Glad to help, and thanks for the rep. (BTW, you don't need to write an essay in the rep comments - a simple thanks is sufficient )