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...
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,

     

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
    Community 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,

     

    • adeel726's avatar
      adeel726
      Frequent Visitor
      6 years ago

      Thanks that worked