Forum Discussion
Income Statement in Financial Reporting Format
Hi,
After reviewing the always-helpful Q&A on this community board, I found the 'cascading subtotals' link (see below) and was able to successfully generate an income statement with gross profit, the correct net income, etc. but it shows revenue/gross profit as positive numbers and COGs/expenses as positive numbers. However, all income statements that I've seen / generated have had positive numbers throughout the statement (positive #s for revenue, positive #s for COGS/expenses, etc with the subtotal / totals adjusted to generate the correct profit). How do you correctly aggregate/calculate general ledger data from the data warehouse and then flip the signs for ONLY COGs/expenses (which are negative in the data warehouse) in order to generate a 'financial reporting formatted' income statement?
http://www.powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/
Thank you in advance.
3 Replies
Sorry, instead of
"...but it shows revenue/gross profit as positive numbers and COGs/expenses as positive numbers..."
I meant to say:
"...but it shows revenue/gross profit as positive numbers and COGs/expenses as NEGATIVE numbers..."
REVISED:
Hi,
After reviewing the always-helpful Q&A on this community board, I found the 'cascading subtotals' link (see below) and was able to successfully generate an income statement with gross profit, the correct net income, etc. but it shows revenue/gross profit as positive numbers and COGs/expenses as negative numbers. However, all income statements that I've seen / generated have had positive numbers throughout the statement (positive #s for revenue, positive #s for COGS/expenses, etc with the subtotal / totals adjusted to generate the correct profit). How do you correctly aggregate/calculate general ledger data from the data warehouse and then flip the signs for ONLY COGs/expenses (which are negative in the data warehouse) in order to generate a 'financial reporting formatted' income statement?
http://www.powerpivotpro.com/2011/09/profit-lossthe-art-of-the-cascading-subtotal/
Thank you in advance.
- Hi,
Could you add another column to your data to indicate that the account be multiplied by -1?
Or maybe you could create new table with the natural accounts in it and a multiplier field or either 1 or -1. Recent versions of PowerBI allows for you to create a new table that you can use it the data model. Then write a measure using DAX to multiple the transactions by the multiplier. Hi Anonymous,
Please check if GarryFarrell's suggestion is helpful. If issue persists, please share some sample data and screenshots about desired results for our analysis.
Best Regards,
Qiuyun Yu
