Row Calculations

[Madelein]

I've got a table with the following information as below:

Date	Fruit	Amount Sold	Sale Amount
201812	Apples	150	75
201901	Apples	50	25
201902	Apples	100	50
201812	Pears	70	35
201901	Pears	80	40
201902	Pears	110	55

I need it represented as below with the differences on a row - not as a column

Date	Fruit	Amount Sold	Sale Amount
201812	Apples	150	75
201902	Apples	100	50
	AppleDifference	-50	-25
201812	Pears	70	35
201902	Pears	110	55
	PearDifference	40	20

Any ideas?

[edgarjb-68]

I have a question on rows calculation on Power BI. How to calculate rows on a table? I wany to create a column called "started" as below. For each row of this column I need the results of this excel like formula on "started" cell C2: =IF(AND(B4=B3;C4-C3<1);"No";"yes")

	A	B	C
1	part	install date	started
2	PAU-0101	26/03/2019 05:17	yes
3	PAU-0101	07/04/2019 18:56	No
4	PAU-0101	07/04/2019 19:56	yes
5	PAU-0102	26/03/2019 05:17	yes
6	PAU-0102	07/04/2019 18:56	No
7	PAU-0102	07/04/2019 19:56	yes

[Madelein]

HI- unfortuantely we cant access onedrive from work. Please put the solution here. Thanks

[Anonymous · Accepted solution]

1) Query 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    ChangedType1 = Table.TransformColumnTypes(ExpandedCount,{{"Date", type text}}),
    Amount1 = Table.AddColumn(ChangedType1, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    #"Removed Other Columns" = Table.SelectColumns(Sales1,{"Fruit", "Date", "Amount Sold", "Sale Amount"}),
    #"Appended Query" = Table.Combine({#"Removed Other Columns", SalesDump2}),
    #"Reordered Columns" = Table.ReorderColumns(#"Appended Query",{"Date", "Fruit", "Amount Sold", "Sale Amount"}),
    SortedRows = Table.Sort(#"Reordered Columns",{{"Fruit", Order.Ascending}})
in
    SortedRows

2) Query2 (Untick Enable Load, we only need it for support)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtDA0UtJRciwoyEktBjIMTQ2ApLmpUqwOWNrSwBBZGixrhCSLqtnAAKIGKg0xOyA1sQgkaw6SNEYzGSZpAZI0MUA1GCZpaAg2F6g1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Fruit = _t, #"Amount Sold" = _t, #"Sale Amount" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Fruit", type text}, {"Amount Sold", Int64.Type}, {"Sale Amount", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"Fruit"}, {{"Count", each Table.AddIndexColumn(_, "ID",1,1), type table [Date=number, Fruit=text, Amount Sold=number, Sale Amount=number]}}),
    ExpandedCount = Table.ExpandTableColumn(GroupedRows, "Count", {"Date", "Amount Sold", "Sale Amount","ID"}, {"Date", "Amount Sold", "Sale Amount","ID"}),
    Amount1 = Table.AddColumn(ExpandedCount, "Amount1", each if [ID] = 1 then [Amount Sold] else [Amount Sold] * -1),
    Sales1 = Table.AddColumn(Amount1, "Sales1", each if [ID] = 1 then [Sale Amount] else [Sale Amount] * -1),
    Grouped_Rows = Table.Group(Sales1, {"Fruit"}, {{"Amount Sold", each List.Sum([Amount1]), type number}, {"Sale Amount", each List.Sum([Sales1]), type number}}),
    HelperCol = Table.AddColumn(Grouped_Rows, "Fruits", each [Fruit] & "Difference"),
    RemovedOtherColumns = Table.SelectColumns(HelperCol,{"Fruits", "Amount Sold", "Sale Amount"}),
    RenamedColumns = Table.RenameColumns(RemovedOtherColumns,{{"Fruits", "Fruit"}}),
    ChangedType1 = Table.TransformColumnTypes(RenamedColumns,{{"Fruit", type text}})
in
    ChangedType1

3) That's it !

[Anonymous]

Try this!

Solution

Please mark this resolved if the solution suits your requirement.