Relationsship by 3rd table?

[Vikinger]

[TABLE="class: kmsg"]
<tbody>[TR]
[TD="class: kmessage-left"]Hi all,

I'm a new member on your board. First of all I want to say sorry for my limited English

I'm currently working on a financial report. In this report I want to analyze our costs per leg (we're a cruise company).

Amongst others I've the following four tables in my datamodel:

Table 1 (called Ship)
Including the columns Shipcode and Shipname

Table 2 (called Saildate)
Including the columns Date and Saildatecode

Table 3 (called deployment)
Including the columns Leg, Shipcode and Saildatecode

Table 4 (Called entries)
Including the columns Amount, Shipcode and Saildatecode


The easiest way to get the costs by leg should be to create relations between the tables "Entries" and "Deployment". But the problem is, that those relations would be n:n. That's why I've tried to set the relations as follows:

Entries[Shipcode] to Ship[Shipcode]
Entries[Saildatecode] to Saildate[Saildatecode]
Deployment[Shipcode] to Ship[Shipcode]
Deployment[Saildatecode] to Saildate[Saildatecode]

In my oppinion the tables entries and deployment should no have a relation, isn't it?

If I want to analyze our costs by leg, I get the total amount over all for each cruise. It looks like powerpivot cannot allocate the costs to the different legs.

An other possibility I've tried was to add a separate ID (1,2,3,4....) to the Deployment table. After that I've created a calculated column in the Entry-Table as follows:

=if(and(Entries[saildatecode]=Deployment[Saildatecode];Entries[Shipcode]=Deployment[Shipcode]);Deployment[Leg];0)

But this formula didn't work.

Do you have an idea how I can get an ID into the entries table or how I've to set the relations for that I can analyze our costs per leg?

Thanks for your help!
Vikinger

<form name="postform" action="/forum/post" enctype="multipart/form-data" method="post"><input name="parentid" type="hidden" value="515"> <input name="catid" type="hidden" value="4"> <input name="action" type="hidden" value="post"> <input name="76fbd925b71dc000d453e48ddb69e7fb" type="hidden" value="1"> <input name="authorname" type="hidden" value="Vikinger"> <input name="subject" class="inputbox" type="text" size="35" maxlength="50" value="Re: Relationsship over 3rd table">
<textarea name="message" class="inputbox" rows="6" cols="60"></textarea>
<input name="submit" title="Click here to submit your message" class="kbutton kreply-submit" type="submit" value="Submit"> <input name="cancel" title="Click here to cancel your message" class="kbutton kreply-cancel" type="reset" value="Cancel"> <small>Note: BBcode and smileys are still usable.</small> </form>
[/TD]
[/TR]
[TR]
[TD="class: kbuttonbar-left"]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Laurent C]

You measure should look like this:

[Costs] := CALCULATE( SUM( Entries[Amount] )
, Deployment
)

Edit: In substance, this tells PowerPivot to calculate the expression SUM( ... ) taking every filter that applies to Deployment into account.

 

[Vikinger]

Thank you, that works!