Need Help Creating a Measure

[Anonymous]

Hi,

I need help with a measure.  My model is shown below.  I have the following tables...

1. Fact - Expense - Shows expense by Job

2. Fact - Units - Shows multiple units by job.  Units are defined by an account name.

3. Dim - Jobs table with hierarchy and attrributes

4. Dates Table

I have two measures that are relevant to my question...

1. Total Expense - Sums the Expense column in the Expense Fact Table

Total Expense = SUM('RM METRICS - EXPENSE'[EXPENSE])

2. Total CM Feet - Sums the Unit column for all rows where the Account Name = CM Feet

Total CM Feet = CALCULATE(SUM('RM METRICS - UNITS'[UNITS]),'RM METRICS - UNITS'[ACCT NAME] = "CM Feet")

I am trying to create a Measure that will calculate "Expense per CM Foot".  However, I only want the Numerator (Expense) to sum up expense on Jobs that have "CM Feet" in the Units Table.  (So not all jobs have amounts in all unit accounts.)  And then the denominator will be the "Total CM Feet" Measure.

Thanks for your advise!

[amitchandak]

You can try making RM Metric and Job join bi-direction.  It should allow you access to the units