Forum Discussion
Creating a summary table from accounting transactions
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
Community Champion
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.