Calculate and summarize rows into a new table

[Lars1]

Hi

I am quite new to Power BI, and i am struggling with this problem.

I have a table which contains order lines and with a Gross Weight on each line.
I need this to be calculated in to a new table where Gross Weight is summarized per order.

Could anyone help me with this ?

 

[lrobbo314]

Using Power Query.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Line No	Order No	Quantity	Gross Weight	Shipping Agent	Shipment Date	Status1	Status2
2	1	123456	1	1.2	UPS	1/1/2021	0	0
3	2	123456	1	1.3	UPS	1/1/2021	0	0
4	3	123456	1	1.2	UPS	1/1/2021	0	0
5	1	894512	1	1	DHL	1/3/2021	1	1
6	2	894512	2	0.8	DHL	1/3/2021	1	1
7	3	894512	1	0.6	DHL	1/3/2021	1	1
8	4	894512	2	0.4	DHL	1/3/2021	1	1
9	1	654871	1	0.2	UPS	1/1/2021	0	0
10	2	654871	2	1.2	UPS	1/1/2021	0	0
11	1	257896	1	1	FedEx	1/5/2021	1	1
12	2	257896	1	0.8	FedEx	1/5/2021	1	1
13	3	257896	2	1.2	FedEx	1/5/2021	1	1
14	4	257896	2	1	FedEx	1/5/2021	1	1
15	5	257896	3	0.8	FedEx	1/5/2021	1	1
16
17
18	Order No	Shipping Agent	Shipment Date	Status1	Status2	Weight Per Order
19	123456	UPS	44197	0	0	3.7
20	894512	DHL	44199	1	1	4
21	654871	UPS	44197	0	0	2.6
22	257896	FedEx	44201	1	1	8.6
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Total = Table.AddColumn(Source, "Weight Per Order", each [Quantity]*[Gross Weight]),
    Group = Table.Group(Total, {"Order No", "Shipping Agent", "Shipment Date", "Status1", "Status2"}, {{"Weight Per Order", each List.Sum([Weight Per Order]), type number}})
in
    Group

 

[Lars1]

Using Power Query.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Line No	Order No	Quantity	Gross Weight	Shipping Agent	Shipment Date	Status1	Status2
2	1	123456	1	1.2	UPS	1/1/2021	0	0
3	2	123456	1	1.3	UPS	1/1/2021	0	0
4	3	123456	1	1.2	UPS	1/1/2021	0	0
5	1	894512	1	1	DHL	1/3/2021	1	1
6	2	894512	2	0.8	DHL	1/3/2021	1	1
7	3	894512	1	0.6	DHL	1/3/2021	1	1
8	4	894512	2	0.4	DHL	1/3/2021	1	1
9	1	654871	1	0.2	UPS	1/1/2021	0	0
10	2	654871	2	1.2	UPS	1/1/2021	0	0
11	1	257896	1	1	FedEx	1/5/2021	1	1
12	2	257896	1	0.8	FedEx	1/5/2021	1	1
13	3	257896	2	1.2	FedEx	1/5/2021	1	1
14	4	257896	2	1	FedEx	1/5/2021	1	1
15	5	257896	3	0.8	FedEx	1/5/2021	1	1
16
17
18	Order No	Shipping Agent	Shipment Date	Status1	Status2	Weight Per Order
19	123456	UPS	44197	0	0	3.7
20	894512	DHL	44199	1	1	4
21	654871	UPS	44197	0	0	2.6
22	257896	FedEx	44201	1	1	8.6
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Total = Table.AddColumn(Source, "Weight Per Order", each [Quantity]*[Gross Weight]),
    Group = Table.Group(Total, {"Order No", "Shipping Agent", "Shipment Date", "Status1", "Status2"}, {{"Weight Per Order", each List.Sum([Weight Per Order]), type number}})
in
    Group

Hi
Thank you so much.

I am sorry, but i should have stated it should be a DAX solution, to be used in Power Bi Tables.
Sorry

 

[lrobbo314]

Never really worked with DAX before. I still had to add a column to the table in Power BI, just like I would have with Power Query, where I multiplied Quantity * Weight.

But then, in DAX, I used the following code and it produced the same output as my first post.

Table =
SUMMARIZE (
    Table5,
    Table5[Order No],Table5[Shipping Agent],Table5[Shipment Date].[Date],Table5[Status1],Table5[Status2],
   "Weight Per Order", SUM ( Table5[Weight Per Order]  )
)

 

[lrobbo314]

Think I figured it out.

Table = 
SUMMARIZECOLUMNS(  
Table5[Order No],
Table5[Shipping Agent],
Table5[Shipment Date].[Date],
Table5[Status1],
Table5[Status2],
"Weight Per Order", SUMX(Table5, Table5[Quantity] * Table5[Gross Weight])  
)

 

[Lars1]

Think I figured it out.

Table =
SUMMARIZECOLUMNS( 
Table5[Order No],
Table5[Shipping Agent],
Table5[Shipment Date].[Date],
Table5[Status1],
Table5[Status2],
"Weight Per Order", SUMX(Table5, Table5[Quantity] * Table5[Gross Weight]) 
)

Perfect.
I think we got it.

THANK YOU SO MUCH