Get sum values of number with a calculated column or measure

[PbiCeo]

Hello everyone,

Could you please help me get sum values.

I have two tables below:
Table1

Year	Status	Type	Category	Lot
2017	Res	01_S	China	1
2017	Res	01_S	China	1
2017	Plan	02_S	Japan	1
2017	Plan	02_S	Japan	1
2018	Res	01_L	USA	1
2018	Plan	01_S	Canada	1
2019	Res	01_M	India	1
2019	Res	01_M	India	1
2020	Plan	02_L	Germany	1

Table 2

Year	Status	Type	Category
2017	Res	01_S	China
2017	Res	01_S	China
2017	Plan	02_S	Japan
2017	Plan	02_S	Japan
2018	Res	01_L	USA
2018	Plan	01_S	Canada
2019	Res	01_M	India
2019	Res	01_M	India
2020	Plan	02_L	Germany

I want to add a calculated column or measure to Table2 with sum values like below "Sum" where values columns match each other between Table1 and Table2:

Year	Status	Type	Category	Sum
2017	Res	01_S	China	2
2017	Res	01_S	China	2
2017	Plan	02_S	Japan	2
2017	Plan	02_S	Japan	2
2018	Res	01_L	USA	1
2018	Plan	01_S	Canada	1
2019	Res	01_M	India	2
2019	Res	01_M	India	2
2020	Plan	02_L	Germany	1

What formula should I use for DAX?

Thanks,

Vladi

[AlB]

Hi @PbiCeo 

Try this for a calc column in Table2:

New Column = 
CALCULATE(SUM(Table1[Lot]), 
          TREATAS(CALCULATETABLE(SUMMARIZE(Table2, Table2[Year], Table2[Status],Table2[Type], Table2[Category])),
                  Table1[Year], Table1[Status], Table1[Type], Table1[Category])
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@PbiCeo , Create a new column at

sumx(filter(Table1, table1[Year] =table2[Year] && table1[Status] table2[Status] && table1[ Type] =table2[Type] && table1[Category] =table2[Category] ),table1[Lot])

Refer this video : https://www.youtube.com/watch?v=czNHt7UXIe8