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
Anonymous
Not applicable

Create a summary table

Hello,

 

I have two tables with différent type of code, each table has a code, an amount column and a date table. 

I want to create a third table wich is a summary of the previous one.

 

With 5 columns : 

- Month

- Countrows of the first table

- Sum of the amount from the first table

- Countrows of the second table

- Sum of the amount from the second table

 

I created a pbix file in order to give you some view over my datas.

 

Best regard,

 

ScrappyS

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

Add this code to a calulated table:

Table = 
NATURALINNERJOIN(
SUMMARIZE(ADDCOLUMNS('Table 1' , "Month_" , MONTH('Table 1'[Month])) , [Month_] , "Countrows T1" , COUNTROWS('Table 1') , "SUM CA T1" , SUM('Table 1'[CA]))
,
SUMMARIZE(ADDCOLUMNS('Table 2' , "Month_" , MONTH('Table 2'[Month])) , [Month_] , "Countrows T2" , COUNTROWS('Table 1') , "SUM CA T2" , SUM('Table 1'[CA]))
)

 

image.png

Br,

J


Connect on LinkedIn

View solution in original post

3 REPLIES 3
tex628
Community Champion
Community Champion

Add this code to a calulated table:

Table = 
NATURALINNERJOIN(
SUMMARIZE(ADDCOLUMNS('Table 1' , "Month_" , MONTH('Table 1'[Month])) , [Month_] , "Countrows T1" , COUNTROWS('Table 1') , "SUM CA T1" , SUM('Table 1'[CA]))
,
SUMMARIZE(ADDCOLUMNS('Table 2' , "Month_" , MONTH('Table 2'[Month])) , [Month_] , "Countrows T2" , COUNTROWS('Table 1') , "SUM CA T2" , SUM('Table 1'[CA]))
)

 

image.png

Br,

J


Connect on LinkedIn
Anonymous
Not applicable

Thanks a lot @tex628 , it's work like a charm !

 

I have just another question : how can i add to my summary table a column wich is the difference between 'table 1'[Sum CA] and 'table 2'[Sum CA] ?

 

('table 1'[Sum CA] - 'table 2'[Sum CA])

 

Best regards,

 

ScrappyS

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.