Forum Discussion
Creating New Teble
Hi erhan_79 ,
Did you tried appending the two tables into one ?
I take it you want a new table within the model?
You can do this easily in Power Query by appending both tables and grouping by the material code.
If you want to do it using DAX, try:
1) Create simple sum measures for each quantity
2) Create a total quantity for each material code in each table using:
T1 total = [SUM T1] + CALCULATE ( [SUm T2], TREATAS ( VALUES ( 'Table 1'[Material Code] ), 'Table 2'[Material Code] ) )T2 total = [SUM T2] + CALCULATE ( [SUm T1], TREATAS ( VALUES ( 'Table 2'[Material Code] ), 'Table 1'[Material Code] ) )3) and finally the DAX to create the new table:
New Table = VAR T1 = ADDCOLUMNS ( 'Table 1', "Material Codes", 'Table 1'[Material Code], "Quantities", [T1 total] ) VAR T2 = ADDCOLUMNS ( 'Table 2', "Material Codes", 'Table 2'[Material Code], "Quantities", [T2 total] ) RETURN SUMMARIZE ( UNION ( T1, T2 ), [Material Codes], [Quantities] )
Hey erhan_79 ,
you can use this DAX statement to create the new table:
Table 2 =
GROUPBY(
UNION(
'Table A'
, 'Table B'
)
, [Material Code]
, "Quantity" , SUMX( CURRENTGROUP() , [Quantity] )
)Hopefully, this provides what you are looking for.
If not, create a pbix that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create sample data, share the xlsx as well.
Regards,
Tom
Much more elegant than my long winded attempt! I didn't know about the CURRENTGROUP function!
learn something new every day...