Want to calculate gross profit and net profit in Powerpivot table

[abalserv]

hi Folks

I've got a chart of accounts: Classification (BS or P&L), Account (Asset, Revenue, Costs, Liabilities)
I have trial balances from 3 companies each have a YTD balance for each month and a PTD balance for each month. (Yes, I know that is not optimal but it is what it is)
So how do I calculate the Gross Profit for each company? What measure do I need to create for each company - also given that I want to come up with a gross profit and net profit (and possibly some other fangled formulas) for each company and in total.
Thank you

 

[Stamoulis]

Can you give us an example of what exactly you want ?

 

[abalserv]

Table A: Chart of accounts...classification e.g. P&L/BS, Account: Asset, liability, Revenue, Cost, Cost of goods sold, category: Sales Product A, COGS Product A , expenses etc. Description e.g. photocopying expenses
Linked via LinkID field
To Company 1
Company 2, Company 3.
In Each company field there is a LinkID, and for each month e.g. September 14 there is a balance for each Description item. I want to create a measure that (for each company) will add up the Revenue items,(already happens via pivot table). I then get a subtotal in my Powerpivot table for that i.e. Revenue Total. Ditto for COGS items. so how do I create a measure that subtracts total COGS items from total Revenue items? to give me gross profit. Then another measure that will add up cost items and subtract from Gross profit to give me net profit....thanks.

 

[scottsen]

I suspect you are using some "implicit" measures today in your pivot table... which is where you grab some numeric column from a table, drag it onto values, and <poof> Sum of ColumnName appears in the pivot.

Those are the devils work

You should always prefer to explicitly create those. [Total ColumnName] := SUM(MyTable[ColumnName])

There is nothing really fancy about a subtraction at that point...

[Total Difference] := [Total ColumnName1] - [Total ColumnName2]

 

[abalserv]

Yes indeed old pivot table habits die hard... Of course after I wrote this and went out for a walk it came to me - just to create a measure that does all that.. thanks again

 

[abalserv]

OK, I got my formulas working - using Calculate. However, now instead of my report appearing as follows..
Month to date
Revenue
Revenue headings..
Total revenue = 50,000
COGS
COGS headings
Total COGS = 35,000
GROSS PROFIT = 15,000 (which I have the correct number for) it's now appearing as another column beside my Month to Date column instead of a single Gross profit entry...
How can I get it to appear as a single entry just for that month? thank you