I'm really only starting to use DAX, and I've scoured the web for an easy-to-understand solution to this problem, but can't find a suitable resolution. As you can see below, I have all the data for a Profit/Loss statement, but want to perform certain calculations (such as Gross Profit) and place it in it's proper position on the pivot table (as a ROW, not a column). The data model/table is simple, with the columns: Account Class/ Sub-class/ Month/ Value.
There are additional items under each Account Class, such as you see under 'c-Expense' (also for instance, if you clicked 'b-Cost of Goods Sold', you would see Payroll, T&E, and many other accounts).
I tried a few different things, such as Calculated Field and also a Calculated Column, but can't get Gross Profit to show as a row in the pivot table... only as a column. I implore you, masters of PowerPivot, to offer your advice and share your genius so that I, too, may one day become as you! Seriously though, I'd really appreciate it.
<colgroup><col><col></colgroup><tbody>
</tbody>
There are additional items under each Account Class, such as you see under 'c-Expense' (also for instance, if you clicked 'b-Cost of Goods Sold', you would see Payroll, T&E, and many other accounts).
I tried a few different things, such as Calculated Field and also a Calculated Column, but can't get Gross Profit to show as a row in the pivot table... only as a column. I implore you, masters of PowerPivot, to offer your advice and share your genius so that I, too, may one day become as you! Seriously though, I'd really appreciate it.
| Account Class/sub-class | Jan-15 |
| a-Income | 500 |
| b-Cost Of Goods Sold | (200) |
| Gross Profit - here = 300 | |
| c-Expense | |
| General & Administrative | (50) |
| Research & Development | (50) |
| Sales & Marketing | (50) |
| c-Expense Total | (150) |
| Net Income/(Loss) | 150 |
<colgroup><col><col></colgroup><tbody>
</tbody>
