cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

 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
1 ACCEPTED SOLUTION
Community Support

we can create a calculated table to meet your requirement:

``````NewTable =
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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Anonymous
Not applicable

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

If I resolve your problem Mark it as a solution and give kudos.
Frequent Visitor

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

we can create a calculated table to meet your requirement:

``````NewTable =
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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Thanks that worked

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors