Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to 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,
Create below calculated table.
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 |
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,
Thanks that worked
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
126 | |
110 | |
87 | |
70 | |
66 |