Calculating value between related fields across multiple fields with different reporting dates

[JRHans09]

I am still learning PBI and we are trying to create a report that will allow us to measure the value between us and our partners based on the amount of goods shipped between us. Sometimes we ask the partner to ship out or receive on our behalf. Other times, the partner asks us to ship out or receive on their behalf. The reporting dates for that value differs depending on whether it is going out or coming in. We would like to create a report that will show us the combined total of shipping out, receiving in, and the balance that is owed with all of our partners across all 4 types of jobs/scenarios.

Below is a diagram showing the 4 types of shipments/cases:

Different Shipment Cases

Below is an example of Case 1 with the related tables

Tonnage Case 1

Below is an example of Case 2 with the related tables:

Tonnage Case 2

Below is an example of Case 3 with the related tables:

Tonnage Case 3

Below is an example of Case 4 with the related tables:

Tonnage Case 4

Below shows the relationships to hopefully help with understanding how the tonnage relates based on the type of shipment and whether the partner agent is the:

1. booking agent (we owe them)

2. origin agent (they owe us)

3. or destination agent (they owe us)

Lastly, here is a sample of what we would like to display for the report layout:

Sample Report Layout 

Since the Active Relationshp for the Agent ID is the key called TAgentID, the report only seems to report tonnage that is linked to that agent, as an Destination Agent (but also lists any other tonnage that would be linked to that agent if they are both a Destination Agent on a particular job, and any other type of agent on a separate job). So, if there are any shipments where the Agent is a Booking Agent only, within the reporting timeframe, those agents are omitted from the table visual.

How do we get all Agents to appear in the report when the active link is TAgentID? Or, maybe this is the wrong question and there are other considerations.

As for tonnage measures, below is a list of what I have created, so far. The measures seem to work correctly, as far as the calculations are concerned, but the tonnage is not being allocated correctly to the agent (whether they were in Case 1, 2, 3 or 4). They only seem to be listed when they were Case 1, if we are displaying the values in a table visual, similar to the above Sample Report Layout.

Here are the measures that I have created, so far:

Total Tonnage = 
SUMX(
    Waybill,
    Waybill[Lbs]
)

Total Tonnage Case 1 - Export = 
CALCULATE(
    [Total Tonnage],
    //Use Departure Date
    USERELATIONSHIP( Waybill[DateDep], Dates[Date] ),
    Waybill[Type] = "Case 1"
)

Total Tonnage Case 2 - Import = 
CALCULATE(
    [Total Tonnage],
    //Use Est Arrival Date
    USERELATIONSHIP( Waybill[DateETA], Dates[Date] ),
    Waybill[Type] = "Case 2"
)

Total Tonnage Case 3 - Export = 
CALCULATE(
    [Total Tonnage],
    //Use Departure Date
    USERELATIONSHIP( Waybill[DateDep], Dates[Date] ),
    Waybill[Type] = "Case 3"
)

Total Tonnage Case 4 - Import = 
CALCULATE(
    Tonnage[Total Tonnage],
    //Use Est Arrival Date
    USERELATIONSHIP( Waybill[DateETA], Dates[Date] ),
    Waybill[Type] = "Case 4"
)

And the final three measures to calculate the balance between us and our partners:

Tonnage Sent = 
[Total Tonnage Case 1] +
[Total Tonnage Case 2]


Tonnage Received = 
[Total Tonnage Case 3] +
[Total Tonnage Case 4]


Tonnage Balance = 
[Tonnage Sent] -
[Tonnage Received]

I think the main question is how do we run a report with Agent[Agent] in the first column in a table visual (as in Sample Report Layout.) and have all the related tonnage be properly linked to this Agent (across all cases)?

***on a related note, when creating multiple measures that are measures of measures, does this reduce the efficiency of the report? Should I try to use VAR as much as possible instead of calculating measures of measures for optimization?

Thanks in advance and please let me know if there are any other questions or further explantion would help. Thanks

[JRHans09 · Accepted solution]

Eventually solved with changes in table structure and evaluation/filter context.

[Greg_Deckler]

You have some fantastic information here but I think that some simplified sample/example data would be extremely useful in understanding your issue.