Do relationships flow through in Powerpivot

[abalserv]

hi Folks
Got a dilemma. I have a chart of accounts (LinkID) and 3 tables with YTD figures in them (linked together with matching linkID). I want to show Gross profit as a single entry under Cost of Goods sold instead of as another column.
I am attempting to replicate David Churchward's rather excellent looking formulas for creating a P&L (and failing so far)
Profit & Loss–The Art of the Cascading Subtotal « PowerPivotPro
He said that it's a many to many relationship - therefore I assume I'll have to create some sort of linking tables which I've tried to do

I've got a chart of accounts (that contains information about revenues/assets etc).
Then I have created another table (using the LinkID) field
called Heading1Link (which has a linkID,Account name,Heading1 Code). This is linked to the Chart of Accounts using the LinkID.
Then I've created another table which has his cascading subtotals. It looks like this..(that is linked via the Heading1_code).
[TABLE="width: 501"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Heading_1_name[/TD]
[TD]Heading_1_Code[/TD]
[TD]Heading_1_Summary[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COGS[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Cost[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EBITDA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Depreciation[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Net Interest Payable[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Profit on sale of assets[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Net Profit before Tax[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Asset[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Liability[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Share Capital and Reserves[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now my question is this...I have created the measure as he suggested..my YTD numbers are from one of the three tables. i.e.'TB_Oct-14'[_Nov_14_YTD_Act] but I keep getting either no numbers at all. Or a message that I need a relationship. I'm now getting googly eyed looking at it so I'd really appreciate some insight..

IF(
COUNTROWS(VALUES(Heading_1_One[Heading_1_name]))= 1,
CALCULATE
(
'TB_Oct-14'[_Nov_14_YTD_Act],
ALL(Heading_1_One[Heading_1_name]),
Heading_1_One[Heading_1_Code] < VALUES(Heading_1_One[Heading_1_Code] )),
BLANK()
)

Thank you

 

[scottsen]

I'm not sure the text of your message matches up with the subject re: relationships, ... not sure what you are asking in that subject. Can i just say Yes?

In the formula, a few things look odd to me:
* Minor: but I would use HASONEVALUE(Heading_1_One[Heading_1_Name]) rather than your COUNTROWS(VALUES()) = 1. They are equivalent, it just looks more readable to me.
* I don't understand your first param to CALCULATE. That looks like a column name... i would expect a measure, or a column wrapped in an aggregate like SUM().
* It's not super clear why you are doing an ALL() on just the Heading_1_name... I think I would expect the whole table there? maybe?
* I suppose I would expect some sort of check on the heading_1_summary as well? (but maybe that is in another measure).

 

[abalserv]

Hi Scott
So if my understanding is correct. I should be able to construct a measure that pulls in data from Table 1 even though it's not directly related to Heading_1_One. The relationship is through LinkID to Heading1Link (table) and then from this table (via) Heading_1_Code to Heading_1_One table (which has the unique values required for the cascading subtotals to work). So if that is the case, what could I be missing?
The idea behind the tutorial is that in order to generate single values for Gross profit/working capital etc an extra table is required to show Powerpivot the pattern. David actually explains it much better than I do...the idea is that this measure will add up the values prior to it. So in the table Gross profit has a value of 3 and a summary value of 1 - the formula (which of course works for him ) adds up the values for Sales/COGS prior to that. (Of course in accounting terms sales are minus/COGS are plus so it does give you a gross profit figure) .
In response to your observations..
I'm using CountRows (because that's what is in his formula)
the param is actually a measure - that's just the naming convention I've been using
He explains All as a way to clear the filter...
as per his article...Profit & Loss–The Art of the Cascading Subtotal « PowerPivotPro
Thank you as always

 

[scottsen]

Well, crap. I had replied that I had some time this weekend to do a quick screen sharing, but apparently that got lost what time zone you in? I'm pacific time and if we can arrange a quick meeting, I don't mind helping straighten you out. If not I will just drop an email to david

 

[abalserv]

Dear Scott
No didn't get that message. But I'm in Europe so about 11 hours difference. I've actually sent David a sample file (I might send it to you as well - if that's OK) of what I require. Sigh, ever have that feeling that what looked like a path is actually a labyrinth....

 

[abalserv]

hi Scott
I actually got it working with the kind help of Phillip Burton on Experts Exchange. Mind you, it still needs another s****** formula to get the final result but it's much closer..it won't work with [account] so I just put in 123 as a dummy number. So what I'm going to try now is another if function either around it or in another calculated column with IF to get my final liability answer
=if(and([Reclassification]="Reversible",sumx(RELATEDTABLE('SA_TB_Oct-14'),[SAA_Nov_14_YTD_Act])<0),-1,123).
Thank you again