Income Statement in Financial Reporting Format

[Anonymous]

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.

[Anonymous]

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.

[v-qiuyu-msft]

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

[GarryFarrell]

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.