# Formula vs measure

### Question

•  I have 3 tables Table1: PropertyID Ownership_percent Table2: PropertyID Date Amount1 Amount2 Amount... Table3: Date Month Year I created a relationship between PropertyID in the tables 1 and 2 as well as between Date in tables 2 and 3. In table 2, I wrote a formula Amount1_Pct = [Amount1] * related(Table1[Ownership_percent]) which worked as expected. I decided to use a measure instead and just copied this formula into it.  The error it returned was "Calculation error in measure 'ECHO_Mortgage_and_MtM'[Measure 1]: The value for 'Mkt_Balance' cannot be determined. Either 'Mkt_Balance' doesn't exist, or there is no current row for a column named 'Mkt_Balance'." The power pivot contains the following: Row label: Year and Month Values: Amount1, Amount2, Amount... Report Filter: Identifier Any help is much appreciated. Thanks

ihorwitz

Wednesday, September 25, 2013 8:17 PM

• Hi,

RELATED() relies on the row-context so it (almost) cannot be used in Measures
to "translate" this to a calculated measure you may try this formula:

```Amount1_Pct_Measure:=SUMX(
VALUES(Table1[Ownership_percent]),
CALCULATE(VALUES(Table1[Ownership_percent]) * SUM(Table2[Amount1])))```

it takes all distinct percentage-values in the current context, multiplies it with the Sum of all associated Amount1-values.
then use SUMX to sum up all values

hth,
-gerhard

- www.pmOne.com -

Thursday, October 24, 2013 9:26 AM

### All replies

• Any suggestions on this one? Thanks!

Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

Thursday, October 24, 2013 8:40 AM
• Hi,

RELATED() relies on the row-context so it (almost) cannot be used in Measures
to "translate" this to a calculated measure you may try this formula:

```Amount1_Pct_Measure:=SUMX(
VALUES(Table1[Ownership_percent]),
CALCULATE(VALUES(Table1[Ownership_percent]) * SUM(Table2[Amount1])))```

it takes all distinct percentage-values in the current context, multiplies it with the Sum of all associated Amount1-values.
then use SUMX to sum up all values

hth,
-gerhard

- www.pmOne.com -

Thursday, October 24, 2013 9:26 AM