Forum Discussion

adeel726's avatar
adeel726
Frequent Visitor
6 years ago
Solved

DISTINCT UNION & Summarized Combined.

Hi 

 

I have two tables and i am trying to make a summary table using "New Table". The result of the two tables should be something similar to "group by" function bases on both tables. the uniqunes is based on Material & Period combination. Previously i have used Summary = DISTINCT(UNION(VALUES('Table2'[Material]),VALUES(Table1[Material]))) function for another project however that is only bringing unique materials across both tables i need it to be unique based on Material & Period and also bring in the Period field. Then by using SUMMARIZE functionality i want to bring the totals across. 

 

i have ways to achieve this by having a few tables and using merge/append etc. but i want one formula that can achieve this. 

 


Table 1 (Data)

MaterialPeriodProduction OrderPlanned Order
PART101/09/20191188
PART101/10/2019 96
PART201/09/20191132

 

Table 2 (Data

MaterialPeriodDelivered Quantity
PART101/09/201920
PART101/09/201920
PART301/10/201920

 

 

Expected Result for the new table.

 
Material

(Table 1 & 2)

Period

(Table 1)

Prod Order

(Table 1)

Planned Orders

(Table 1)

Delivery Quantity (Table2)

Part101/09/2019118840
Part101/10/20190960
Part201/09/20191132 
Part301/10/2019  10
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi adeel726 ,

     

    we can create a calculated table to meet your requirement:

     

    NewTable = 
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Material", [Material], "Period", [Period] ),
            SELECTCOLUMNS ( 'Table2', "Material", [Material], "Period", [Period] )
        )
    ),
    "Prod Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Production Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Planned Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Planned Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Delivery Quantity",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table2'[Delivered Quantity] ),
            FILTER ( 'Table2', 'Table2'[Material] = M && 'Table2'[Period] = P )
        )
)

     

     


    Best regards,

     

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    adeel726 instead of summarizing the tables, the best practice would be to add part no and date table in your model and set relationship of these tables with your these transactio tables and from there you can easily visualize the data the way you want.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Create below calculated table.

     

    Table =
    Var First=SUMMARIZE(Sheet4,Sheet4[Material],Sheet4[Period],"Plan order",SUM(Sheet4[Planned Order]),"Prod order",SUM(Sheet4[Production Order]),"Delivered Quantity",0)
    Var Second_tab=SUMMARIZE(Sheet5,Sheet5[Material],Sheet5[Period],"Plan order",0,"Prod order",0,"Delivered Quantity",SUM(Sheet5[Delivered Quantity]))
    return
    UNION(First,Second_tab)
     
     
    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.
    • adeel726's avatar
      adeel726
      Frequent Visitor
      6 years ago

      Hi Pravin

       

      Close, the only remaining issue is that from the second table it is creating a new line as opposed to adding the delivery quantity to the same line. As you can see row 5 has a delivery quantity of 30 but that should be in row 1 as the material and preiod are the same. Row 6 is fine as the material is different and there are no records prior that match the material/period combination.

       

      FYI the formula i uses is below. 

      Table =
      Var Tab1=SUMMARIZE(Table1,[Material],[Period],"Plan order",SUM(Table1[Planned Orders]),"Prod order",SUM(Table1[Prod Order]),"Delivered Quantity",0)
      Var Tab2=SUMMARIZE('Table2',[Material],[Period],"Plan order",0,"Prod order",0,"Delivered Quantity",SUM('Table2'[Open Quantity])) return
      UNION(Tab1,Tab2)

       

      MaterialPeriodProd OrderPlanned OrdersDelivery Quantity 
      Part101/09/201911880
      Part201/09/201911320
      Part101/10/20190960
      Part201/10/2019000
      Part101/09/20190030
      Part301/10/20190020
      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi adeel726 ,

         

        we can create a calculated table to meet your requirement:

         

        NewTable = 
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Material", [Material], "Period", [Period] ),
            SELECTCOLUMNS ( 'Table2', "Material", [Material], "Period", [Period] )
        )
    ),
    "Prod Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Production Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Planned Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Planned Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Delivery Quantity",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table2'[Delivered Quantity] ),
            FILTER ( 'Table2', 'Table2'[Material] = M && 'Table2'[Period] = P )
        )
)

         

         


        Best regards,