account ledger flows summary

eibcga2014

New Member
Joined
Sep 7, 2014
Messages
15
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?
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sorry to reply with a total non-answer. This would be a great opportunity for me to practice my (terrible) M2M skills, but I am crazy busy right now.

The best I can do is add some links to smart folks talking about M2M issue, and hope it helps. And yes, your intuation is correct... you can't "self relate". If required, you could dupe the table I guess?


Alberto Ferrari : PowerPivot and Many to Many Relationships

http://social.technet.microsoft.com...many-to-many-relationships-in-power-pivotssas

Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering – SQLBI
-tabular-models.aspx

A mystifying and awesome solution for many 2 many « PowerPivotPro
 
Upvote 0
Hi,
Can you please say more about id_trans_lineitems. Is that supposed to be a unique transaction identifier or with your example, DR Cash CR Rev 001 for both Cash and Revenue. By which I mean that cash, for instance would be account 100 and id_trans_lineitems would 0001. Also, in the Detail section why do you have a field for amount when you also have DR and CR. Wouldn't initial JE just be DR Cash CR Rev. Not sure how you set things up.
Thanks.
 
Upvote 0
Hi Greg,

I wanted to clarify the above by saying that the desired pivot table of a "flows summary" should show a summary of all transactions flowing to and from each account, not the detail of every single transaction line item (like a typical general ledger transaction listing report would do). For example, all cash paid for groceries expense would show as a total use of the cash account, and so on for each account. In other words, basically, a flows summary is a summary of all the journal entries made to, for example, the cash account, summarized by account. This "flows summary" information is not offered in most accounting software without a lot of work. The desired pivot table would address this.

The TRANSACTION table stores the journal entries as detailed above. Each transaction is assigned a unique identifier (that is, a journal entry number) in the id_trans field, the parent key. Since every transaction must have at least two child records related to the parent transaction record (i.e., a record to debit one account, and another record to credit another account), these child records are tracked in a child table called DETAILS.

The DETAILS table (the bridge table), tracks all the individual transaction line items relating to the related parent record in the TRANSACTION table. The id_trans_lineitems field, the parent key to the DETAILS table, is a unique identifier and is not necessary for this problem and can be ignored (it's mentioned since theoretically every table should have a parent key). What's critical is that the id_trans foreign key in the DETAILS table matches the id_trans parent key in the TRANSACTION table. This enables me to print "trial balances" and "general ledger details" by account, as is common with most accounting software.

The ACCOUNT table has a parent key "id_account" represents the account number, the unique identifier. So for example, the Cash account could have number 1000, and Groceries expense account could have number 5200. The foreign key for the id_account would be included in the DETAILS table, since this is where all the detailed entries are made to each account.

To keep the database simple, the amount field in the DETAILS table is where the debit amount or credit account is originally stored for each transaction child record entered by the user. A positive amount in the amount field is a debit. A negative amount in the amount field is a credit. A journal entry balances if all debits total all credits (i.e, the sum is zero, not just at the journal entry level but also for the entire general ledger). For reporting purposes, however, the debit and credit fields are just formulas referring back to the amount column, so that the reports (that is.., trial balance report, and general ledger report) can show a separate debit column and a separate credit column with proper amounts shown, totals, etc.

The accounting database is kept in FileMaker and works quite well. But additional analysis is needed and Power Pivot would do great for that from the exported records from FileMaker into Excel. The "flows summary" needed in this problem is already working well in FileMaker, but would like to do the same in Power Pivot by getting a pivot table of the "flows summary".

Thanks again for your consideration.
 
Last edited:
Upvote 0
Thanks Scottsen. I have read a lot from Alberto to help me get the basic many-to-many relationships working properly in the pivot table, as shown above in the CALCULATE formulas. But do need more help to code it in Power Pivot to summarize things a certain way using a self-join relationship. I'll take a look at your suggested links. Thanks!
 
Upvote 0
Thanks that is helpful.

To clarify, "The foreign key for the id_account would be included in the DETAILS table, since this is where all the detailed entries are made to each account." So you are saying that there should be an id_account in the Detail Table? In your table layout above I do not see it.
 
Upvote 0
Also, if Detail is going to be your bridge table, it would need to have both contact and account. At first glance, the detail table seems to be to be acting as a 2nd fact table.
 
Upvote 0
Creating a cash flow statement or sources/uses of funds in Power Pivot seems like a great objective to me. Have not attempted it.

I'll start by saying that it is not immediately clear to me that what you describe is a m2m situation. The traditional m2m, as I understand, is where a customer has many accounts and an account might have a customer as well as that customers spouse.

I'll take a shot at setting things up the way you would need to to create a statement of cash flows. May not be until next week however. Also, would be willing to do a short screen share to go over the data model.
 
Upvote 0
The Transaction table has the contact, since the contact applies to all the transaction detail line items. If the contact was added in the Details table instead, it would be redundant and subject to error. All the transaction details relate to the same transaction, so only need to enter the contact name once, which is how it's set up. Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top