account ledger flows summary

[eibcga2014]

I have the following tables and relevant fields and DAX measures...

CONTACT (table to track customers, suppliers, employees, etc.)
id_contact (primary key)
contact_name
contact_drbalance=CALCULATE(SUM(detail[debit]),detail)
contact_crbalance=CALCULATE(SUM(detail[credit]),detail)

ACCOUNT (table to track assets, liabilities, equity, revenue and expenses)
id_account (primary key)
id_trans_lineitems (foreign key)
account_name
account_drbalance=CALCULATE(SUM(detail[debit]),detail)
account_crbalance=CALCULATE(SUM(detail[credit]),detail)

TRANSACTION (table to track double-entry bookkeeping journal entries)
id_trans (primary key)
id_contact (foreign key)
id_trans_lineitems (foreign key)
date
memo
reference

DETAIL (bridge table for TRANSACTION & ACCOUNT to track transaction line items)
id_trans_lineitems (primary key)
id_trans (foreign key)
amount
comment
debit=IF(amount>0,amount,"")
credit=IF(amount<0,amount,"")

An account can have many transactions and a transaction can have many accounts as required by double-entry bookkeeping. So it's a many-to-many relationship. Debits are entered as positive amounts . Credits are entered as negative amounts. For reporting purposes, debits and credits are shown separately by using two calculated columns.

All the records have been exported from the database and into Excel for analysis in Power Pivot.

I can run pivot tables to get total debit and credit amounts by contact, by account, etc. Good so far.

I would like to be able to have a pivot table that shows the flows in and out of a particular account, i.e., the sources and uses of an account, summarized by the related accounts. For example,

Let's say there's a bookkeeping entry to receive cash for $500 cash. The journal entry to record this transaction would be:

Debit Cash $500
Credit Revenue $500
To record receipt of cash from sale to Customer A on September 13/14

Let's say there's another bookkeeping entry to buy groceries and fuel for $100 cash. The journal entry to record this transaction would be:

Debit Groceries $30
Debit Fuel $70
Credit Cash $100
To record purchase of groceries and fuel from Grocer A on September 13/14

In the above example, I can produce the trial balance (the pivot table) to show the accounts and their balances:

Debit Credit

Cash $400
Revenue $500
Groceries $30
Fuel $70
----------------------------------------
Total $500 $500

The problem I'm having is to show a new pivot table of, for example, the cash account, which summarizes the sources and uses of the particular account, summarized by related accounts:

ACCOUNT DEBIT CREDIT

SOURCES
Revenue $500

Total $500 $0

USES
Groceries $30
Fuel $70

Total $0 $100

I'm thinking in order to get this type of pivot table I would need some kind of self-join bridge table (a recursive relationship) in order to show related records of the transactions summarized in a certain way. I can do this in a database, but don't know how to do this in Power Pivot.

Any guidance please?

 

[eibcga2014]

I picked the Cash account on which to do a flows summary only as an example. I would like to be able to do a flows summary pivot table for ANY account. I can already to this using FileMaker Pro via a self-join relationship, but I'm trying to duplicate it in Power Pivot. I know it can be done in PP. I just don't know the proper coding to do it. Thank you so much for your help. I love PP!