Answered by:
Formula vs measure

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
Question
Answers

Hi,
RELATED() relies on the rowcontext 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 percentagevalues in the current context, multiplies it with the Sum of all associated Amount1values.
then use SUMX to sum up all valueshth,
gerhard www.pmOne.com 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Friday, October 25, 2013 7:54 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee, Owner Monday, November 25, 2013 7:02 AM
All replies

Any suggestions on this one? Thanks!
Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)
Answer an interesting question? Create a wiki article about it! 
Hi,
RELATED() relies on the rowcontext 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 percentagevalues in the current context, multiplies it with the Sum of all associated Amount1values.
then use SUMX to sum up all valueshth,
gerhard www.pmOne.com 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Friday, October 25, 2013 7:54 PM
 Marked as answer by Ed Price  MSFTMicrosoft employee, Owner Monday, November 25, 2013 7:02 AM