PowerPivot Table trouble

[gkleos718]

Excel question.JPG


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 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.

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

[AlexandraT]

Can you post a sample with the 3 tables and the pivot you can make then what you want to achieve (manually)? The layout you want to achieve is unclear as you present it in your post. Try going to "Advanced" on the forum when you post and then Manage Attachments.
Alex

[gkleos718]

Alex,

Attached is an example of the tables and what I can produce from the PivotTable. What I need is for the G account information to be separated from the B account information. So I'd like the column headers to go G account and B accounts across the top header, then underneath G accounts it says AP AR and underneath that it says 234 and 2345. For the B accounts it should go B accounts then Payable and Receivables then underneath that it say PMTS under payable, and traditional and other under receivables. The values of the pivot table need to be the amount from each table for the invoice with a grand total along all of the rows in one column not split into three separate totals for the values.

Thank you for the help

Example.xlsx