Forum Discussion
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
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])) )
Br,J
3 Replies
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])) )
Br,J
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
Anonymous , you can append merge and group by
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/
