Calculate a Measure using a constant from an unrelated table

SteviebLGX

New Member
Joined
Dec 7, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 7, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

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
Top