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 :)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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).
 
Upvote 0
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 :)
 
Upvote 0
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 :)
 
Upvote 0
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....:)
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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