Forum Discussion
Creating a summary table from accounting transactions
I have an accounting transactions table that contains the following fields:
Company - two digits numeric
Cost Centre - four digits numeric
Account code - 5 digits numeric
Account Type - 1 digit numeric (first digit of account code)
Account Group Ten - 5 digit numeric (first four digits of account code with a 0 appended)
Year - YYYY
Month - MM
Year / month - YYYYMM
Amount
I need to create two separate tables by Company, Cost Centre and Account Code with column headings Year / Month.
The first table (a profit and loss table) has to be just those transactions with an Account Code between 00000 to 39999 and should contain the totals of Amount for each Year / Month.
The second table (a balance sheet table) has to be just those transactions with an Account Code between 60000 to 99999 and should contain accumulated totals of Amount to date by Year Month (accumulated from the very beginning, not by year)
Any help geatly appreciated.
Hi PaulBoyes,
Based on your description, you want to get the summary table which “account code” between 00000 to 39999 or “account code” between 60000 to 99999,right?
You can follow below two methods to get the specify records.
1. Summarize function:Table 00000 to 39999 =
SUMMARIZE( FILTER(Sheet1,AND(Sheet1[Account Code]>=0,Sheet1[Account Code]<=39999)),Sheet1[Company],Sheet1[Account Code],Sheet1[Account Type],Sheet1[Account Group Ten],Sheet1[Year/Month],"Total",SUM(Sheet1[Amount]))Table 60000 to 99999 =
SUMMARIZE( FILTER(Sheet1,AND(Sheet1[Account Code]>=60000,Sheet1[Account Code]<=99999)),Sheet1[Company],Sheet1[Account Code],Sheet1[Account Type],Sheet1[Account Group Ten],Sheet1[Year/Month],"Total",SUM(Sheet1[Amount]))2. Visual level filter:
Set other fields to “Don’t summarize”, modify the amount summary option to sum.
Regards,
Xiaoxin Sheng
2 Replies
1. For import accounting transaction table and duplicate it so you have two copies of that dataset.
2. Then via query editor for the profit and loss table filter the accounting code column. You don't need to additionally create totals for year / month as in if you would have used any visual to see amount by year / month it would aggregate it itself by year / month. For example column chart.
3. As step 2 filter out accounting code column and then use DAX functions such as TOTALYTD to calculate year-to-date value of amount.
Hi PaulBoyes,
Based on your description, you want to get the summary table which “account code” between 00000 to 39999 or “account code” between 60000 to 99999,right?
You can follow below two methods to get the specify records.
1. Summarize function:Table 00000 to 39999 =
SUMMARIZE( FILTER(Sheet1,AND(Sheet1[Account Code]>=0,Sheet1[Account Code]<=39999)),Sheet1[Company],Sheet1[Account Code],Sheet1[Account Type],Sheet1[Account Group Ten],Sheet1[Year/Month],"Total",SUM(Sheet1[Amount]))Table 60000 to 99999 =
SUMMARIZE( FILTER(Sheet1,AND(Sheet1[Account Code]>=60000,Sheet1[Account Code]<=99999)),Sheet1[Company],Sheet1[Account Code],Sheet1[Account Type],Sheet1[Account Group Ten],Sheet1[Year/Month],"Total",SUM(Sheet1[Amount]))2. Visual level filter:
Set other fields to “Don’t summarize”, modify the amount summary option to sum.
Regards,
Xiaoxin Sheng
