Power Pivot Grand Total Calculation Error

[Avra]

I have a Power Pivot Table with a Grand Total calculation error. Here is the database:

MR Excel.xlsm
	A	B							I							P												AB				AF			AI							AP	AQ
1									Mailing List 1							Capitalization 2												Records 3				Convertible Class A Voting 4			Units Summary 5							Capital Summary 6
2
3		Mr Excel Database
4
5		Name							1							2												3				4			5							6
6		Mr Excel
7		Mrs Excel
8		Excel Jr
9		Total
10
Mr Excel Database

Cell Formulas
Range	Formula
I1	I1	=C4&" "&ZeoThermalDatabase[[#Headers],[1]]
P1	P1	=J4&" "&ZeoThermalDatabase[[#Headers],[2]]
AB1	AB1	=Q4&" "&ZeoThermalDatabase[[#Headers],[3]]
AF1	AF1	=AC4&" "&ZeoThermalDatabase[[#Headers],[4]]
AI1	AI1	=AG4&" "&ZeoThermalDatabase[[#Headers],[5]]
AP1	AP1	=AJ4&" "&ZeoThermalDatabase[[#Headers],[6]]

Here is the Pivot Table:

MR Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Mr Excel Technologies LLC Capitalization Summary 2014
3			UNDILUTED BASIS		FULLY DILUTED BASIS
4			Class A Voting		Convertible Class A Voting			Units Summary		Capital Summary
5
6		Date of Purchase	All
7
8		Investor Name	Undiluted Basis Class A Units	Ownership Percent	From Convertible Debt	From Outstanding Warrants	Total Units Controlled	Fully Dilluted Units	Diluted Ownership Percent	Class A Invested Capital	Convertible Debt Invested Capital	Warants Invested Capital	Other Promissory Notes Payable Net of Interest Due	Total Invested Capital	Capital Invested Percent
9		Excel Jr	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
10		Mr Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
11		Mrs Excel	2,500,000	33.33%	0	0	2,500,000	2,500,000	33.33%	$ 25,000	$ -	$ -	$ -	$ 25,000	33.33%
12		Grand Total	7,499,999	100.00%	0	0	7,499,999	7,499,999	100.00%	$ 75,000	$ -	$ -	$ -	$ 75,000	100.00%	What's causing this error?
13
14
Mr Excel

Why is the Grand Total calculation in error. The Grand Total should be 7,500,000.

 

[RoryA]

You haven't set that up for sharing, I'm afraid.

 

[Avra]

You haven't set that up for sharing, I'm afraid.

Ok...let's try this again:

Mr. Excel - Google Drive

 

[RoryA]

Yes I can access that.

That is very odd! If you format the source column in the data model as a Decimal data type rather than whole number, it fixes the problem but the model seems to auto-correct it back to whole number whenever I refresh the pivot. I'm not sure if it's a bug in your workbook, or in PP, but will do some testing.

 

[Avra]

It seems that this only happens with each investor receiving 2,500,000 shares. When I use other combinations of shares to get the 7,500,000 total (2,500,001, 2,500,000, 2,499,999) I do not get the calculation error.

 

[Avra]

Hi @rory,

I think I figured it out. In Microsoft 365, I went to Power Pivot>Data Model>Manage. From here, I formatted each column. Now everything adds up correctly.

I am curious how your testing works out. Please let me know.

Thanks,

Steve