Using case statement to create measure net income

user060355
user060355 ⚪️
in Magic ETL

I have the following case statement to categorize my quickbooks transactions into their correct categories:

case 
	when `Group 2 Name` = 'Sales' or `Group 2 Name` = 'Sales Returns' then 'Sales'
	when `Group 2 Name` = 'Direct Costs' or `Group 2 Name` = 'Protein Powder' then 'Costs of Goods Sold'
	when `Group 0 Name` = 'Other Income/Expense' then 'Other Income'
    else 'Expense'
end

 

This works perfectly, but I'd like to be able to create a calculation that provides me with the net income.  The net income should equal:

'Sales' - 'Expenses' - 'Costs of Goods Sold' + 'Other Income'

 

But I'm not sure how to convert this category beast mode into a numeric calculation.  The value for the totals in each categories can be found by performing:

sum(`Amount`)

on the transactions within the category.

 

Any help is appreciated!

Best Answer

  • MichelleH
    MichelleH 🟤
    Answer ✓

    You should be able to apply the same logic as your grouping beast mode, just returning the net amount instead of the label, and then summing it all:

     

    sum(case 
	when `Group 2 Name` = 'Sales' or `Group 2 Name` = 'Sales Returns' then `Amount`
	when `Group 2 Name` = 'Direct Costs' or `Group 2 Name` = 'Protein Powder' then -1*`Amount`
	when `Group 0 Name` = 'Other Income/Expense' then `Amount`
    else -1*`Amount`
end)

     

Answers

Categories