Calculate differences of each order

[gauravnarchal]

How can I calculate the total difference (Balance) of each order and display the result as shown below?

Data - Table 1

Reference	Order	Diff
1096989	BLQ00888183	728.61
	BLQ00888183	-735.00
1097010	BLQ00888194	1580.01
	BLQ00888194	-790.00
	BLQ00888194	-790.00
1097012	BLQ00888200	-0.26
1096965	BLQ00888207	-17.94
1097023	BLQ00888211	1146.00
	BLQ00888211	-580.00
	BLQ00888211	-580.00
1097019	BLQ00888224	-18.70
1097105	BLQ00888281	2080.10
	BLQ00888281	-2165.00
1097140	BLQ00888285	-30.54
1097141	BLQ00888287	-0.94
1097154	BLQ00888297	-4.19
1097165	BLQ00888300	5034.33
	BLQ00888300	-1595.00
	BLQ00888300	-1595.00
	BLQ00888300	-1925.00
1097186	BLQ00888303	-44.80
1097241	BLQ00888309	-31.63
1097253	BLQ00888310	-10.18
1097257	BLQ00888311	-56.84
0	BLQ00888312	0.00
1097376	BLQ00888315	9228.99
	BLQ00888315	-4690.00
	BLQ00888315	-4690.00
1097402	BLQ00888319	-14.39
1097460	BLQ00888321	-57.94
1097457	BLQ00888322	-40.15
1097463	BLQ00888323	17092.26
	BLQ00888323	-6565.00
	BLQ00888323	-5355.00
	BLQ00888323	-5355.00
1097478	BLQ00888324	-16.86
1097469	BLQ00888325	3148.55
	BLQ00888325	-3190.00
1097549	BLQ00888328	3387.65
	BLQ00888328	-3495.00
1097565	BLQ00888330	266.12
1097594	BLQ00888331	-13.82
1097628	BLQ00888332	-45.40

Result

Reference	Order	Diff	Balance
1096989	BLQ00888183	728.61	(6)
	BLQ00888183	-735.00	-
1097010	BLQ00888194	1580.01	0
	BLQ00888194	-790.00	-
	BLQ00888194	-790.00	-
1097012	BLQ00888200	-0.26	-0.26
1096965	BLQ00888207	-17.94	-17.94
1097023	BLQ00888211	1146.00	-14.00
	BLQ00888211	-580.00	-
	BLQ00888211	-580.00	-
1097019	BLQ00888224	-18.70	-18.70
1097105	BLQ00888281	2080.10	-84.90
	BLQ00888281	-2165.00	-
1097140	BLQ00888285	-30.54	-30.54
1097141	BLQ00888287	-0.94	-
1097154	BLQ00888297	-4.19	-
1097165	BLQ00888300	5034.33	-80.67
	BLQ00888300	-1595.00	-
	BLQ00888300	-1595.00	-
	BLQ00888300	-1925.00	-
1097186	BLQ00888303	-44.80	-44.80
1097241	BLQ00888309	-31.63	-31.63
1097253	BLQ00888310	-10.18	-10.18
1097257	BLQ00888311	-56.84	-56.84
	BLQ00888312	0.00	0.00
1097376	BLQ00888315	9228.99	-151.01
	BLQ00888315	-4690.00	-
	BLQ00888315	-4690.00	-
1097402	BLQ00888319	-14.39	-14.39
1097460	BLQ00888321	-57.94	-57.94
1097457	BLQ00888322	-40.15	-40.15
1097463	BLQ00888323	17092.26	-182.74
	BLQ00888323	-6565.00	-
	BLQ00888323	-5355.00	-
	BLQ00888323	-5355.00	-
1097478	BLQ00888324	-16.86	-16.86
1097469	BLQ00888325	3148.55	-41.45
	BLQ00888325	-3190.00	-
1097549	BLQ00888328	3387.65	-107.35
	BLQ00888328	-3495.00	-
1097565	BLQ00888330	266.12	266.12
1097594	BLQ00888331	-13.82	-13.82
1097628	BLQ00888332	-45.40	-45.40

[v-chenwuz-msft · Accepted solution]

Hi @gauravnarchal ,

You can try use this code to do that in power query.

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZM7bsQwDETv4joS+BfZpk6TepH7XyP6ILa4XmCBlMI8kcMR9XgcCGHhcXwcn1/fAO6Ozv3UyKvh8fPxOG5aaaxT6ZcbIOxASD+hOlS4355iaQFvlFWXNoBgdClQyf4IC9NEtEFgq73WWYR4RxCHORR77r+U0m2/UZazPS6iaR29tpNASM58lCDomdwiWVohtCtRFEjEqFUYqsqGYELaCmebHFV2IiYhtVs/iRQfz4AVWCrzs8slFtTQf0hB22xuiZjrJFL9JCiNxhBzeqzGF6L7qzKuNj3drYq2hKxHtOorIUjiWLTrebklizhSCurfIeI24RSL2H2jn6VRWYASMUfDHvj1KGLJGi3faaclj0Y0+/TpdauSApqfABsE/X2fm1pM7f6AS1LWt9Ls2jwR61/0zG0zFgkZGTGK11cd1t7jFqBKvj76MXurr7z7vC5xGdS88TyiJrPaF+BEQhIy80eufiFGaUxe+WvtL/TzCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Order = _t, Diff = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", Int64.Type}, {"Order", type text}, {"Diff", type number}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Reference"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Reference"}, {{"balance", each List.Sum([Diff]), type nullable number}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type" , "Query1", each (if [Reference] <> null then Query1(#"Grouped Rows", [Reference]) else "-"))
in
    #"Invoked Custom Function"

Query1:

let
    Source = (a as table,b) => let s1 = Table.SelectRows(a,each [Reference] = b)[balance]{0} in s1
in
    Source

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@gauravnarchal Sorry, I feel like I am missing some vital piece of information. For example, where does the (6) come from in the first row of the second table?

[gauravnarchal]

@Greg_Deckler 

If you sum the diff of order BLQ00888183 will give you the result -6

[Greg_Deckler]

@gauravnarchal So, first, if you haven't already, you will want to do a Fill down for your Reference column so that the appropriate Reference # appears on each row. Once you do that, put your Reference column in a table visual along with Order. Then just add your Diff column with a default SUM aggregation.