DISTINCT UNION & Summarized Combined.

[adeel726]

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)

Material	Period	Production Order	Planned Order
PART1	01/09/2019	11	88
PART1	01/10/2019		96
PART2	01/09/2019	11	32

Table 2 (Data

Material	Period	Delivered Quantity
PART1	01/09/2019	20
PART1	01/09/2019	20
PART3	01/10/2019	20

Expected Result for the new table.

Material (Table 1 & 2)	Period (Table 1)	Prod Order (Table 1)	Planned Orders (Table 1)	Delivery Quantity (Table2)
Part1	01/09/2019	11	88	40
Part1	01/10/2019	0	96	0
Part2	01/09/2019	11	32
Part3	01/10/2019			10

[Anonymous]

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]

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)

Material	Period	Prod Order	Planned Orders	Delivery Quantity
Part1	01/09/2019	11	88	0
Part2	01/09/2019	11	32	0
Part1	01/10/2019	0	96	0
Part2	01/10/2019	0	0	0
Part1	01/09/2019	0	0	30
Part3	01/10/2019	0	0	20

[v-lid-msft · Accepted solution]

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]

Thanks that worked

[parry2k]

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