Show ONE gross profit figure in ROW instead of column

[abalserv]

hi Folks
Still stuck on this one. I've posted a file with what I have and with what I want. I'm currently using Powerpivot so want to do it that way...Have a chart of accounts and set of balances for 3 groups - PTD/YTD for each item.
I want to generate a gross profit figure that will appear under Revenue and COGS rather than in another column. Any suggestions on how to get what I want
[TABLE="width: 790"]
<colgroup><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]What I've got[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]What I want[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]PTD[/TD]
[TD]YTD[/TD]
[TD]Gross Profit PTd[/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD]PTD[/TD]
[TD]YTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Widgets[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]849[/TD]
[TD][/TD]
[TD]Widgets[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gadgets[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gadgets[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]2500[/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]2500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COGS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steel[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]127[/TD]
[TD][/TD]
[TD][/TD]
[TD]COGS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nuts[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Steel[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]127[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total COGS[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]227[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nuts[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total COGS[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]227[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wages[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD]Gross Profit[/TD]
[TD="align: right"]849[/TD]
[TD="align: right"]2273[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Electricity[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]230[/TD]
[TD][/TD]
[TD][/TD]
[TD]Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]Wages[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Costs[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]830[/TD]
[TD][/TD]
[TD][/TD]
[TD]Electricity[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]230[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Office[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total Costs[/TD]
[TD="align: right"]184[/TD]
[TD="align: right"]830[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Net Profit[/TD]
[TD="align: right"]665[/TD]
[TD="align: right"]1443[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

This sort of reshaping is usually a bit of a pain.

* If you data is fair "fixed" in # or rows and such, i would consider just using Cube Formulas.
* You might be able to pull off something with Named Sets, but... I'm less sure there.
* You can get cray-cray.

I was at this meeting October Seattle MEUG Meetup - Seattle Modern Excel Users Group (MEUG) (Redmond, WA) - Meetup where Derek did an amazing job on his accounting report that feels similiar to what you going going on. I think that above link might get you to a recording of the presentation but I am not 100% sure. If you can't find it, let me know and I will see what i can do there.

Basically, on whatever table you have "Total COGS" on, you just add another row called "Gross Profit" and write a measure that changes it's calculation based on if the current row equals "Gross Profit".

Fancy := IF (VALUES(Table[RevenueDescription]) = "Gross Profit", [Calc Gross Profit], [Do Other Calc])

 

[abalserv]

Ah, something that looks so simple when it's done in good old fashioned Excel is turning out to be a total clusterf***** . Got all my tables set up tidily with Chart of accounts etc so it looks as though I'll have to re-jig something to get this...and not sure I have time to do that..but will have a go. Thanks for your help

 

[Tianbas]

how about converting your pivot into CUBEVALUE Formulas (copy the pivot go to options-> OLAP tools -> convert to formulas). This converts all parts of the pivot in formulas in Excel cells.

This will give you an option to get data out of PowerPivot in a kind of static report that you can design like you want to. The disadvantage is that you cant drill into the data like in a pivot but slicers would still work.