PowerPivot Question

[gkleos718]

G Accounts	B Accounts
			AP Account	AR Account	Payables	Rec
		Beg Balance	234	2345	Pmts	Purchases	Trad	Other	Grand Total
Company	Invoice
1234	1	100	$200				100				400
	2	100			$250				200
	3				$300					300	600
	4		$10				20				30
	5
21345	6
	7
	8
	9

<tbody>
</tbody>

Sorry this is a little confusing since it is not in proper table form but first time poster so still new to the system. I am using PowerPivot to construct a table containing these elements. The information for the table is coming from 3 large data tables all connected by the invoice number. I am struggling to create the table in the format shown above. The biggest issue is putting the G accounts before the B accounts as that is the format I need it in. I also need to be able to run this table every so often and have the ending balance from the last table be the beginning balance on the next report, IF any of the invoices grand totals are not equal to 0.

So the report needs to pull the information from each report if the invoice matches each other and be able to separate the data into the various categories. So for example: Invoice 1 is a $100 transaction in one of the G reports on the AP side account 234, it also appears on the B reports side under payables with a sub category of payments. The rows are then added up as a grand total. In terms of the data table there is two tables for G accounts and one for B accounts, either of the G accounts need to match with the B account, the G accounts do not have to match with each other for the invoices. So i currently have the relationships for powerpivot as invoice number for G1 is the same as B invoice number and B invoice is in a relationship with G2 as well.

I know this is a lot of information and is probably a little confusing to understand, I'm still new to excel and would appreciate any help I can receive.

Thanks

 

[gkleos718]

I am able to pull all the proper information for the amounts pertaining to the invoices from all 3 tables. I'm just struggling to format it correctly. Currently, the grand totals for the rows are separated into the 3 different values in the tables and I need them to be grouped into one for the whole row. I don't think I will be able to create a PivotTable with the G accounts and B accounts separated so I have just renamed the columns to properly fit the columns.

Long story short biggest thing I need is to eliminate the grand totals for the values of the rows and conglomerate them into one total for the row.

Thanks