Show ONE gross profit figure in ROW instead of column

abalserv

New Member
Joined
Oct 25, 2013
Messages
30
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 :)
What I've gotWhat I want
RevenuePTDYTDGross Profit PTdRevenuePTDYTD
Widgets4501000849Widgets4501000
Gadgets5001500Gadgets5001500
Total9502500Total9502500
COGS
Steel56127COGS
Nuts45100Steel56127
Total COGS101227Nuts45100
CostsTotal COGS101227
Wages100500Gross Profit8492273
Electricity50230Costs
Office34100Wages100500
Total Costs184830Electricity50230
Office34100
Total Costs184830
Net Profit6651443

<colgroup><col><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
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

New Member
Joined
Oct 25, 2013
Messages
30
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

Board Regular
Joined
Apr 29, 2014
Messages
101
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.
 

Forum statistics

Threads
1,077,684
Messages
5,335,654
Members
399,031
Latest member
Morto

Some videos you may like

This Week's Hot Topics

Top