Calculation of Total s from a column based on Document Numbers

Question

Hello guys, its very important and difficult task that I am faced with. Below is a table&nbsp;where you can see that the data is in ascending order as per dates and also as per the Document number. If we see the same number document is used for clearing of one bill and all of its Receipts or Replies. Therefore the last column contains Total Values of Bill, Receipt and Exchange diff which combined knocks off the Bill or only a slight portion of Bill is left unpaid (That is why receipt is in negative). But what I want in Column five (5) is that based on sum of document numbers if the sum total of that document number is more than 0, that is, it is outstanding by some portion, then the remaining (Outstanding amount) show in against the Bill row of that Document Number and also receipt value multiplied by negative should show against receipt row of that particular document number. If the sum total for any particular document number is 0, then only the negative of receipt amount and Exchange diff should show against Receipt row of that document number and the Exchange diff row resepcetively.&nbsp;Kindly please if anyone can formulate such condition. Its basically for calculation of late payment surcharge on bill receipts above the due date but the problem is that many bills have more than one receipts or other factors corresposnding to it. (Bill is represented by a unique document number).Jul-17Bill8000025&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 36,816,920Jul-17Exchange diff8000025&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,317Jul-17Receipt8000025&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (36,822,237)Aug-17Bill8000297&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 77,788,412Aug-17Exchange diff8000297&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,566Aug-17Receipt8000297&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (77,794,978)Sep-17Bill8000551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 109,868,311Sep-17Exchange diff8000551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,375Sep-17Receipt8000551&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (109,874,686)Oct-17Bill8000674&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100,688,746Oct-17Exchange diff8000674&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (61,802)Oct-17Receipt8000674&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (100,626,944)Nov-17Bill8000839&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 110,543,290Nov-17Receipt8000839&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (110,527,940)Nov-17Exchange diff8000839&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (15,350)Dec-17Receipt8001030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (288,773,588)Dec-17Bill8001030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 288,618,568Dec-17Exchange diff8001030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 155,020Jan-18Bill8001136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 284,152,877Jan-18Receipt8001136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (284,140,251)Jan-18Exchange diff8001136&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (12,626)Feb-18Bill8001279&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 254,178,038Feb-18Receipt8001279&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (254,281,739)Feb-18Exchange diff8001279&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 103,701Mar-18Bill8001546&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 208,551,174Mar-18Receipt8001546&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (208,509,371)Mar-18Exchange diff8001546&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (41,803)Apr-18Bill8001666&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 205,417,715Apr-18Receipt8001666&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (205,437,363)Apr-18Exchange diff8001666&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 19,648May-18Bill8001929&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 120,783,466May-18Receipt8001929&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (120,753,579)May-18Exchange diff8001929&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (29,887)Jun-18Bill8002067&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 119,256,071Jun-18Receipt8002067&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (119,256,071)Jul-18Bill8000032&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 94,446,347Jul-18Receipt8000032&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (94,435,913)Jul-18Exchange diff8000032&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (10,434)Aug-18Bill8000180&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 117,393,902Aug-18Receipt8000180&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (117,388,740)Aug-18Exchange diff8000180&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (5,162)Sep-18Bill8000407&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 75,838,002Sep-18Receipt8000407&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (75,833,010)Sep-18Exchange diff8000407&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (4,992)

vijay_a_verma · Accepted Answer

Solution uploaded to -&nbsp;https://1drv.ms/x/s!Akd5y6ruJhvhuSIghsHUEzQkwFJ-?e=8XJUft&nbsp;M-codelet
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column3"}, {{"All", each _, type table [Column1=nullable date, Column2=nullable text, Column3=nullable number, Column4=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "ProcessFunction", each ProcessFunction([All])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column3", "All"}),
    #"Expanded ProcessFunction" = Table.ExpandTableColumn(#"Removed Columns", "ProcessFunction", {"Column1", "Column2", "Column3", "Column4", "Result"}, {"Column1", "Column2", "Column3", "Column4", "Result"})
in
    #"Expanded ProcessFunction"(TableName)=&gt;
let
    #"Added Index" = Table.AddIndexColumn(TableName, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Sum", each List.Sum(#"Added Index"[Column4])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each if [Sum]&gt;0 then
(if [Index]=0 then [Sum] else if [Index]=2 then -1*[Column4] else 0)
else if [Index]&gt;0 then -1*[Column4] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Sum"})
in
    #"Removed Columns"

Forum Discussion

Calculation of Total s from a column based on Document Numbers

2 Replies

Related content

Business Requirement Document Template

RSCustomDaxFilter Documentation

Power BI Documentation

Linking documents

MoM calculation

Jul-17	Bill	8000025	36,816,920
Jul-17	Exchange diff	8000025	5,317
Jul-17	Receipt	8000025	(36,822,237)
Aug-17	Bill	8000297	77,788,412
Aug-17	Exchange diff	8000297	6,566
Aug-17	Receipt	8000297	(77,794,978)
Sep-17	Bill	8000551	109,868,311
Sep-17	Exchange diff	8000551	6,375
Sep-17	Receipt	8000551	(109,874,686)
Oct-17	Bill	8000674	100,688,746
Oct-17	Exchange diff	8000674	(61,802)
Oct-17	Receipt	8000674	(100,626,944)
Nov-17	Bill	8000839	110,543,290
Nov-17	Receipt	8000839	(110,527,940)
Nov-17	Exchange diff	8000839	(15,350)
Dec-17	Receipt	8001030	(288,773,588)
Dec-17	Bill	8001030	288,618,568
Dec-17	Exchange diff	8001030	155,020
Jan-18	Bill	8001136	284,152,877
Jan-18	Receipt	8001136	(284,140,251)
Jan-18	Exchange diff	8001136	(12,626)
Feb-18	Bill	8001279	254,178,038
Feb-18	Receipt	8001279	(254,281,739)
Feb-18	Exchange diff	8001279	103,701
Mar-18	Bill	8001546	208,551,174
Mar-18	Receipt	8001546	(208,509,371)
Mar-18	Exchange diff	8001546	(41,803)
Apr-18	Bill	8001666	205,417,715
Apr-18	Receipt	8001666	(205,437,363)
Apr-18	Exchange diff	8001666	19,648
May-18	Bill	8001929	120,783,466
May-18	Receipt	8001929	(120,753,579)
May-18	Exchange diff	8001929	(29,887)
Jun-18	Bill	8002067	119,256,071
Jun-18	Receipt	8002067	(119,256,071)
Jul-18	Bill	8000032	94,446,347
Jul-18	Receipt	8000032	(94,435,913)
Jul-18	Exchange diff	8000032	(10,434)
Aug-18	Bill	8000180	117,393,902
Aug-18	Receipt	8000180	(117,388,740)
Aug-18	Exchange diff	8000180	(5,162)
Sep-18	Bill	8000407	75,838,002
Sep-18	Receipt	8000407	(75,833,010)
Sep-18	Exchange diff	8000407	(4,992)

Customer No	Column1	Column2	Column3	Column4	Column5	Column6
1	Jan-22	Bill	8001136	284,152,877	1
1	Jan-22	Receipt	8001136	-284,140,251	3
1	Jan-22	Exchange diff	8001136	-12,626	4
1	Feb-22	Bill	8001279	254,178,038	1
1	Feb-22	Receipt	8001279	-254,281,739	3
1	Feb-22	Exchange diff	8001279	103,701	4
1	Mar-22	Bill	8001546	208,551,174	1
1	Mar-22	Receipt	8001546	-208,509,371	3
1	Mar-22	Exchange diff	8001546	-41,803	4
2	Jan-22	Bill	22222	284,152,877	1
2	Jan-22	Receipt	22222	-284,140,251	3
2	Jan-22	Exchange diff	22222	-12,626	4
2	Feb-22	Bill	8001279	254,178,038	1
2	Feb-22	Receipt	8001279	-254,281,739	3
2	Feb-22	Exchange diff	8001279	103,701	4
2	Mar-22	Bill	8001546	208,551,174	1
2	Mar-22	Receipt	8001546	-208,509,371	3
2	Mar-22	Exchange diff	8001546	-41,803	4
2	Apr-22	Bill	8001666	205,417,715	1
2	Apr-22	Receipt	8001666	-205,437,363	3
2	Apr-22	Exchange diff	8001666	19,648	4
2	Jul-22	Bill	8000025	36,816,920	1
2	Jul-22	Receipt	8000025	-31,822,237	3
2	Jul-22	Exchange diff	8000025	5,317	4
3	Jan-22	Bill	22222	284,152,877	1
3	Jan-22	Receipt	22222	-284,140,251	3
3	Jan-22	Exchange diff	22222	-12,626	4
3	Feb-22	Bill	8001279	254,178,038	1
3	Feb-22	Receipt	8001279	-254,281,739	3
3	Feb-22	Exchange diff	8001279	103,701	4
3	Mar-22	Bill	8001546	208,551,174	1
3	Mar-22	Receipt	8001546	-208,509,371	3
3	Mar-22	Exchange diff	8001546	-41,803	4