Performing Calculations Across Multiple Tables

[chris_m]

Hello,

I need a bit of guidance on how to ensure that the calculations I'm running maintain accuracy as I add more dimensions to a matrix. I couldn't really think of a much more specific or exciting title for my post, sorry 🙂

A little bit of context / background - to help understand the data, plus so I can hide sensitive information. 

I work in the Ocean Freight industry, and what I'm trying to do is build a model that analyses the forecast data provided to us by one of our customers. The main objective of this post is to work out how to take the forecast data our customer is providing us, and calculate an equipment plan (i.e. containers)

• I have two date dimensions: Snapshot Date (The date the forecast was provided to us) and Supply Date (the date the particular item is required to be supplied)
• I have two fact tables of forecast data - Stable Demand and Unstable Demand. Unfortunately the layout of these tables is not the same so I can't merge them. (at least not with my level of expertise)
• There are multiple categories of demand within each table also. The most stable category has all the required attributes to it that allows us to plan effectively, mainly an ocean freight carrier (e.g. Maersk).

Some of the categories do not have a carrier, so what I have tried to do is to calculate the amount of equipment required for all demand categories by carrier and supply date based on the amount allocated to that carrier for that week.

Carrier Factor =
CALCULATE ( [Number of Containers with Shipments] )
/ CALCULATE (
[Number of Containers with Shipments],
ALL ( 'Stable Demand'[Carrier Name] )
)

Where [Number of Containers with Shipments] is the sum of equipment in the most stable demand category. 

From here I calculate the sum of equipment in the Unstable Demand table

Number of Containers Unstable =
CALCULATE ( SUM ( 'Unstable Demand'[Containers] ) )

and the sum of equipment in the Stable Demand table, that doesn't have an ocean carrier assigned.

Number of Containers Without Shipments =
CALCULATE (
    [Number of Containers Firmed],
    'Stable Demand'[Order status] = "No Ocean Carrier"
)

Then I have additional measures that multiplies each of these measures by the original [Carrier Factor] measure. 

Number of Containers Unstable x Carrier Factor =
CALCULATE ( [Number of Containers Unstable] ) * [Carrier Factor]

Number of Containers without Shipments x Carrier Factor =
CALCULATE (
    [Number of Containers Without Shipments],
    ALL ( 'Stable Demand'[Carrier Name] )
)
    * [Carrier Factor]

Then a final measure combining them all: (please excuse any DAX newbie techniques)

Carrier Factored Volume =
CALCULATE ( [Number of Containers with Shipments] )
    + ( CALCULATE ( [Number of Containers without Shipments x Carrier Factor] ) )
    + ( CALCULATE ( [Number of Containers Unfirmed x Carrier Factor] ) )

If I create a matrix visual, and filter for one snapshot period, and use the carrier as the row, and the supply year and week as columns, and the final measure as the values, I get a result that 'seems right' (I think)

However, if I want to add more dimensions to this matrix, such as the Port where it needs to be delivered, or the type of container (Dry or Refrigerated), will my calculations still hold up? (or is there a smarter way for me to perform this calculation process?)

How / what is the best way to test that this works like it is meant to?

Thanks in advance!

[Anonymous]

Hi @chris_m,

Could you please  share sample data of your tables?

Thanks,
Lydia Zhang

[chris_m]

Hi Lydia,

What is the best and most secure way for me to do this?

[Anonymous]

Hi @chris_m,

You can share me the sample data via Private message.

Thanks,
Lydia Zhang