Using case statement to create measure net income

user060355
user060355 Member
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 Coach
    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

  • MichelleH
    MichelleH Coach
    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)

     

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories