Using case statement to create measure net income

user060355

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!

MichelleH

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)

 

MichelleH

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)

 

jaeW_at_Onyx

Just FYI i have an entire tutorial playlist dedicated to tackling financial reporting in YouTube.  If you're trying to construct an income statement using transactional data, here's a video that might help you get the layout you're looking for: https://www.youtube.com/watch?v=YgevJkjeFqw&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=27&t=354s