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>
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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])
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top