Creating New Teble

[erhan_79]

Hi There ;

i need to create a new table from two other table.I have below two table Table A and Table B :

i need a to create a new "table C " which includes sum of the quantities based on material codes as below 

Could you pls help me to create a new table , thanks in advance 

[jaideepnema · Accepted solution]

Hi @erhan_79 ,

Did you tried appending the two tables into one ?

[TomMartens]

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

[PaulDBrown]

@TomMartens 

Much more elegant than my long winded attempt! I didn't know about the CURRENTGROUP function!

learn something new every day...

[jaideepnema]

@erhan_79 yes append the two tables into one and then create the required visualization using the new table. 

Please accept this as a solution if your question is answered !!

Appreciate A Kudos 😀

[PaulDBrown · Accepted solution]

@erhan_79 

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

[jaideepnema · Accepted solution]

Hi @erhan_79 ,

Did you tried appending the two tables into one ?

[erhan_79]

thats a good idea @jaideepnema 🙂