none
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

Answers

  • 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
    Answerer

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!

    Thursday, October 24, 2013 8:40 AM
    Owner
  • 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
    Answerer