Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago
Solved

Creating New Teble

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 

 

 

 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

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

     

     

6 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    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's avatar
      PaulDBrown
      Community Champion
      5 years ago

      TomMartens 

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

      learn something new every day...

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    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's avatar
    jaideepnema
    Solution Sage
    5 years ago

    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 😀