Calculated Field Total in P-Table is Wrong/odd! 1

[acjim]

Hi,

I've put a pivot table together that includes the following calculated field:

Field Formula
Field1 ='Bed Days' /'Total BedDays' *Cost

Now each field seems to be fine and when added manually (out of the p-table) the total is what I expected, BUT the Grand Total in the table is out by about 10%. I've read in the help that pivot totals are not simple summations but totals from the background data, in this case a considerable number of data items are hidden from the table - could this be affecting the result?

Any assistance would be fantastic!

thanks
Jim

 

[SkipVought]

Jim,

Instead of talking to some therortical principle, how 'bout a concrete example?

Please post a subset of SOURCE DATA that DEMONSTRATES this problem and explain in detail, the CONDITIONS that affect this problem.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

Hi Skip,

Er, there's a lot of data but here's the p-table:

Ward Bed Days. Proportion Cost
BYRD 540 £23,762.00
CAVE 487 £0.00
CLAR 472 £15,652.00
DAY 1 £4,624.00
DICK 747 £15,233.00
DOWL 327 £2,761.00
EDIS 527 £4,234.00
EDIT 105 £3,264.00
ELIO 710 £8,265.00
EVEL 701 £4,315.00
FLO 563 £2,058.00
GRAY 598 £3,827.00
HALD 939 £13,146.00
HARD 894 £10,081.00
HERR 822 £5,205.00
JAME 955 £8,998.00
JENN 1155 £12,398.00
JONS 950 £14,304.00
WEST 558 £5,977.00
SALM 1295 £14,715.00
Grand Total 13346 £183,063.64
sum of the Proportion Cost is = 172819

Hope the format shows what I mean, here's some data:

Hospital||Ward||Specialty||Consultant||BedDays||Cost||TotalBedDays
NPH||BYRD||ENT||FARR||6||£23,762.00||540
NPH||CLAR||GENSURG||MCDPJ||13||£15,652.00||472
STM||SALM||GENSURG||VAIC||6||£14,715.00||1295

Hope you can read that - each line in the source has a total; cost and bed-days for the ward.

Thanks for the interest

 

[SkipVought]

I was expecting that you would pare the SOURCE DATA down and perform a PT on IT that would DEMONSTRATE the problem and you would be able to point to a SPECIFIC AREA where this problem is occurring!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

I think I've got the answer now, it seems that the Grand Total is worked out from the Totals in the source data - ie

Grand Total Proportion Cost = sum(bed days)/sum(total bed days) * sum(cost)
NOT
Grand Total Proportion Cost = sum(bed days/total bed days * cost)

Is there any way to change the way this is done?

Thanks
Jim

 

[SkipVought]

I can't help you if I can't see SPECIFIC source data. Why don't you post EVERY SOURCE RECORD for ONE WARD (the smalles number of records)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

Here you go:

Hospital Ward Specialty Consultant Bed Days Cost TotalBedDays
NPH HARD CARDIO CARR 0 £10,081.00 894
NPH HARD ENDO BROD 1 £10,081.00 894
NPH HARD GENMED BETH 11 £10,081.00 894
NPH HARD GENMED BROD 138 £10,081.00 894
NPH HARD GENMED CHUE 0 £10,081.00 894
NPH HARD GENMED COHDL 0 £10,081.00 894
NPH HARD GENMED HARMG 9 £10,081.00 894
NPH HARD GENMED HIGC 0 £10,081.00 894
NPH HARD GENMED JACM 203 £10,081.00 894
NPH HARD GENMED KEAA 1 £10,081.00 894
NPH HARD GENMED LUBD 0 £10,081.00 894
NPH HARD GENMED ORND 5 £10,081.00 894
NPH HARD GENMED PITM 149 £10,081.00 894
NPH HARD GENMED SAYG 4 £10,081.00 894
NPH HARD GENMED SENR 4 £10,081.00 894
NPH HARD GENMED SHAP 192 £10,081.00 894
NPH HARD GENMED STEK 176 £10,081.00 894
NPH HARD GENMED VIOA 0 £10,081.00 894
NPH HARD GENSURG CHASJD 0 £10,081.00 894
NPH HARD GENSURG RENS 1 £10,081.00 894
NPH JENN CARDIO BETH 327 £12,398.00 1155
NPH JENN CARDIO SENR 355 £12,398.00 1155
NPH JENN CARDIO STENG 47 £12,398.00 1155
NPH JENN CARDIO VIOA 85 £12,398.00 1155
NPH JENN GENMED BETH 130 £12,398.00 1155
NPH JENN GENMED BROD 0 £12,398.00 1155
NPH JENN GENMED HARMG 1 £12,398.00 1155
NPH JENN GENMED JACM 0 £12,398.00 1155
NPH JENN GENMED KEAA 1 £12,398.00 1155
NPH JENN GENMED ORND 0 £12,398.00 1155
NPH JENN GENMED SENR 71 £12,398.00 1155
NPH JENN GENMED VIOA 138 £12,398.00 1155

Real Cost for Both 12398+10081 = 22479
Calculated Cost 2049/31740 * 350396 = 22620.082

2 wards as the total works for 1.
Cheers
jim

 

[SkipVought]

Remove the grand total.

replace with this formula
[tt]
=SUM(Proportion Cost)
[/tt]
whatever that range is. I'd put this total ABOVE the PT.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

Hi,

I know what you mean but this will only work if I remove the subtotals. I think I should try to rethink the source data so the Pivot Table can work properly.

Thanks for your time Skip!

Jim

 

[SkipVought]

I though there were NO SUBTOTALS cuz you said your PT looked like this...
[tt]
Ward Bed Days. Proportion Cost
BYRD 540 £23,762.00
CAVE 487 £0.00
CLAR 472 £15,652.00
DAY 1 £4,624.00
DICK 747 £15,233.00
DOWL 327 £2,761.00
EDIS 527 £4,234.00
EDIT 105 £3,264.00
ELIO 710 £8,265.00
EVEL 701 £4,315.00
FLO 563 £2,058.00
GRAY 598 £3,827.00
HALD 939 £13,146.00
HARD 894 £10,081.00
HERR 822 £5,205.00
JAME 955 £8,998.00
JENN 1155 £12,398.00
JONS 950 £14,304.00
WEST 558 £5,977.00
SALM 1295 £14,715.00
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

Unfortunately the user of the table wants it to be interactive, with the option of a compacted table (ward only) like the example above and a full table with ward -> consultant. The latter has subtotals for each ward.

I feel like I've messed you about, sorry!

 

[SkipVought]

Add a column to your source data with your formula. Then do your PT using the new column instead of a PT Formula

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[acjim]

Thanks, that works well - should have gone with that method initially.

have a twinkly star for your patience,

jim