Calculate a Measure using a constant from an unrelated table

[SteviebLGX]

I have written a measure that calculates the volume of a number of items for an order. I now want to write a measure that would calculate how many cartons would be needed for that order. There are three carton sizes which I have defined in a separate, but unrelated table. In essence the calculation would be

NumberOfSmallCartons:=TotalVolume/SmallCartonVolume
NumberOfMediumCartons:=TotalVolume/MediumCartonVolume
NumberOfLargeCartons:=TotalVolume/LargeCartonVolume

The Carton Volume Table is something like this;

Small,900000
Medium,125000
Large,200000

I need to be able to reference the Carton Volume Table by creating a 'temporary' relationship by virtue of 'Small', 'Medium', or 'Large'.

Or do it a better way.

Any advice/suggestions would be appreciated. Thanks

(I have asked this on another forum, but with no response. Apologies if this is a repeated question for some)

 

[KRice]

Welcome to the Board. Please provide the link to the cross-post so that potential helpers can review and determine whether to invest time responding.

Is your Small volume 90000 rather than 900000? What are the constraints on your problem? Are you seeking to have the least quantity of "over" volume, minimize the total number of containers, etc?

 

[SteviebLGX]

Welcome to the Board. Please provide the link to the cross-post so that potential helpers can review and determine whether to invest time responding.

Is your Small volume 90000 rather than 900000? What are the constraints on your problem? Are you seeking to have the least quantity of "over" volume, minimize the total number of containers, etc?

Yes, you are correct - it should be 90,000.

The purpose of the calculation is to determine how many containers of each type would be needed. It is a stepping stone calculation really.

What I want to know is if I have a table of 'constants' like this how can I reference them in Measures.

(I will try and find the cross-reference).

 

[SteviebLGX]

Also raised here

Calculate a Measure using a constant from an unrelated table - Microsoft Q&A