Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
88 | |
87 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
85 | |
63 | |
54 |