Calculation in Pivot Table

[SchneeBaer]

I am trying to create a pivot table that pulls number of hours and hourly rate from two different data sources, and then calculates an extended amount.

To illustrate, below is a sample data source:

	A	B	C	D
Name	Type
Jane	A
Jane	B
Jane	C
Jane	D
Jane	E
John	A
John	B
John	C
John	D
John	E

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Hrs[/TD]
[TD="align: right"]Rate.[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]

</tbody>

Sheet1​

Putting this data into a Pivot Table and adding a calculated field for Hours * Rate, the following displays. The sum for Jane is 3750, and the sum for John is 1250. Note that the Grand Total is not the sum of the rows.

	A	B	C	D	E
Name	Type	Hours	Rate	Total
Jane	A
B
C
D
E
John	A
B
C
D
E
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]750[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1250[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]250[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]35[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]350[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]450[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]48125[/TD]

</tbody>

Sheet3​

Collapsing the rows shows the following:

	A	B	C	D	E
Name	Type	Hours	Rate	Total
Jane
John
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]18750[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]6250[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]48125[/TD]

</tbody>

Sheet3​

I've tried switching the summary function on the rate to average, but that only averaged the two rates in the Grand Total line.

	A	B	C	D	E
Name	Type	Hours	Rate	Total
Jane
John
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]18750[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]6250[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]48125[/TD]

</tbody>

Sheet3​

Is there a way to accomplish an accurate total column?

 

[Colo87x]

Hi Baer

Same happens to me, the best way around this is using a helper column in the raw data. ie Column F "=SUM(Hours*Rate)" then when including this column in your PivotTable it will calculate correctly, Let me know if this helps?

 

[SchneeBaer]

Well, darn! I was really hoping for a better answer. This doesn't seem like that unusual of a need.

Unfortunately, my real data set is much more complex, and is pulling directly from 2 SQL tables, one of which has the hours and the other having the rate. For security purposes, we don't want the rates stored with the user-entered hours, and we have too much data to extract it all to Excel.

Thanks for the response though. The only thought I have is to create a SQL view specifically for this pivot table.

 

[pvr928]

Hi SchneeBaer

Have a look at PowerPivot / PowerBI - this newish technology can pull from multiple sources and perform calculations and filters on the data.

It is available from Excel 2010 onwards.

There are plenty of resources out there; this is a start: https://powerpivotpro.com/what-is-power-pivot/

Cheers

pvr928