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
1	Name	Type	Hrs	Rate.
2	Jane	A	10	25
3	Jane	B	20	25
4	Jane	C	30	25
5	Jane	D	40	25
6	Jane	E	50	25
7	John	A	5	10
8	John	B	15	10
9	John	C	25	10
10	John	D	35	10
11	John	E	45	10

<tbody>
</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
3	Name	Type	Hours	Rate	Total
4	Jane	A	10	25	250
5		B	20	25	500
6		C	30	25	750
7		D	40	25	1000
8		E	50	25	1250
9	John	A	5	10	50
10		B	15	10	150
11		C	25	10	250
12		D	35	10	350
13		E	45	10	450
14	Grand Total		275	175	48125

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Collapsing the rows shows the following:

	A	B	C	D	E
3	Name	Type	Hours	Rate	Total
4	Jane		150	125	18750
5	John		125	50	6250
6	Grand Total		275	175	48125

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
3	Name	Type	Hours	Rate	Total
4	Jane		150	25	18750
5	John		125	10	6250
6	Grand Total		275	17.5	48125

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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