Add row of Total revenue and total expenses with their calculated amount using m language

[Anonymous]

hello i have a table combining expenses and revenues with their values in the table below as a small example:

Type	Level 1	Level 2	Amount
Revenue	Sales	Product A	100
Revenue	Sales	Product B	300
Expense	Payroll Expenses	Hr Cost	20
Expense	Payroll Expenses	Accountant	15
Expense	Business Expenses	Attorney Fees	5
Expense	Business Expenses	Consultant Fees	5
Expense	Financial Fees	Fee A	30
Expense	Financial Fees	Fee B	50

i want to manually add total revenue and total expenses but i want the amount of total revnue to be sum(amount) if Type = Revenue and total expense amount to be sum(amount) if type="Expense",

so how can i do it?

[Anonymous · Accepted solution]

I found a workaround way.

first import your data with powerquery . Then copy it as a new query. and groupby it . Then tranform data.

all done by mouse click.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Level 1", type text}, {"Level 2", type text}, {"Amount", Int64.Type}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Type", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Type"}, {{"Amount", each List.Sum([Amount]), type number}}),
#"Replaced Value" = Table.ReplaceValue(#"Grouped Rows","Revenue","Total Revenue",Replacer.ReplaceText,{"Type"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Expense","Total Expense",Replacer.ReplaceText,{"Type"}),
#"Transposed Table" = Table.Transpose(#"Replaced Value1"),
#"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each try [Column1]-[Column2] otherwise "Net Profit"),
#"Transposed Table1" = Table.Transpose(#"Added Custom"),
#"Renamed Columns" = Table.RenameColumns(#"Transposed Table1",{{"Column1", "Type"}, {"Column2", "Amount"}})
in
#"Renamed Columns"

second append it to orginial data.

[Anonymous]

I just have one more question @Anonymous  what if i have dates? in the original data and i want to filter by date? what can i do

[Anonymous]

Groupby you can select multiple conditions.

[Anonymous]

Youre a life saver man thank you very much

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

Total CC =
SUMX ( FILTER ( Data, Data[Type] = EARLIER ( Data[Type] ) ), Data[Amount] )

[Anonymous]

hello i dont want it in ddax because i want my table to look like this so i need t add them mmanually with m query and i want results like this:

Type	Level 1	Level 2	Amount	New Column
Revenue	Sales	Product A	100	100
Revenue	Sales	Product B	300	300
Expense	Payroll Expenses	Hr Cost	20	20
Expense	Payroll Expenses	Accountant	15	15
Expense	Business Expenses	Attorney Fees	5	5
Expense	Business Expenses	Consultant Fees	5	5
Expense	Financial Fees	Fee A	30	30
Expense	Financial Fees	Fee B	50	50
Total Revenue	Total Revenue	null	null	400
Total Expense	Total Expense	null	null	125
Net Profit	Net Profit	null	null	275

[Anonymous]

HI I am a new to M function.

Do you mean like this? 

= Table.AddColumn(#"Trimmed Text", "GroupBy",(data)=>List.Sum(Table.SelectRows(#"Trimmed Text",(b)=>b[Type]=data[Type])[Amount]))

[Anonymous]

hello @Anonymous  yes something like that i appreciate your help but im trying to add rows too like i want the final result to be as follow:

Type	Level 1	Level 2	Amount	New Column
Revenue	Sales	Product A	100	100
Revenue	Sales	Product B	300	300
Expense	Payroll Expenses	Hr Cost	20	20
Expense	Payroll Expenses	Accountant	15	15
Expense	Business Expenses	Attorney Fees	5	5
Expense	Business Expenses	Consultant Fees	5	5
Expense	Financial Fees	Fee A	30	30
Expense	Financial Fees	Fee B	50	50
Total Revenue	Total Revenue	null	null	400
Total Expense	Total Expense	null	null	125
Net Profit	Net Profit	null	null	275

as you see i want to add 3 new rows called Total revenue , Total Expenses and net profit and then do the calculations