Do relationships flow through in Powerpivot

abalserv

New Member
Joined
Oct 25, 2013
Messages
30
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).
Heading_1_nameHeading_1_CodeHeading_1_Summary
Revenue1
COGS2
Gross Profit31
Cost4
EBITDA51
Depreciation6
Net Interest Payable7
Profit on sale of assets8
Net Profit before Tax91
Asset10
Liability11
Share Capital and Reserves12

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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

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

New Member
Joined
Oct 25, 2013
Messages
30
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-known Member
Joined
Mar 16, 2014
Messages
1,263
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

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

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

Forum statistics

Threads
1,081,834
Messages
5,361,594
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top