Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PaulBoyes
Regular Visitor

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.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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]))

 

Capture3.PNG

 

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]))

 

Capture4.PNG

 

2. Visual level filter:
Set other fields to “Don’t summarize”, modify the amount summary option to sum.

Capture.PNGCapture2.PNG

 


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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]))

 

Capture3.PNG

 

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]))

 

Capture4.PNG

 

2. Visual level filter:
Set other fields to “Don’t summarize”, modify the amount summary option to sum.

Capture.PNGCapture2.PNG

 


Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
ankitpatira
Community Champion
Community Champion

@PaulBoyes

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.