iterator with var table filters

[Dellis81]

Hello!

 I have come across an unexpected behavior inside an iterator (AverageX) function.    I have two Variables utilizing a calculatetable function, and when used outside the iterator -works great.

VAR AnnualValue =CALCULATE ( [Amount (dbcr balance)], dbamount, TermTable )

However, it I stick this same calculate inside the iterator - returns the sum, not the AvergeX   I would expect 137,900/3= $45,967

Please note - the Depreciation measure works perfectly - the only exception it only has one calculatetable filter inside the iterator.

NP Term Principal Proceeds =
VAR DbAmount =
    CALCULATETABLE (
        'Consolidated Transactions',
        FILTER ( 'Consolidated Transactions', [Amount (dbcr balance)] < 0 )
    )
VAR TermTable =
    CALCULATETABLE (
        COASetup,
        COASetup[SubHeaderID] IN { 1221, 1222, 1223, 1224, 1225 }
    )
VAR RepValue =
    CALCULATE ( - [Amount (dbcr balance)], dbamount, TermTable )

VAR ExclCurrentYrAvg =
    AVERAGEX (
        FILTER ( VALUES ( 'CALENDAR'[Year] ), 'CALENDAR'[Year] < YEAR ( TODAY () ) ),
        CALCULATE ( [Amount (dbcr balance)] + 0, DbAmount, TermTable )
    )
VAR AnnualValue =
    CALCULATE ( [Amount (dbcr balance)], dbamount, TermTable )
VAR ReportValue =
    IF ( HASONEVALUE ( 'CALENDAR'[Year] ), AnnualValue, ExclCurrentYrAvg )
RETURN
    CALCULATE ( - ReportValue )

NP Term Principal Proceeds = 
VAR DbAmount =
    CALCULATETABLE (
        'Consolidated Transactions',
        FILTER ( 'Consolidated Transactions', [Amount (dbcr balance)] < 0 )
    )
VAR TermTable =
    CALCULATETABLE (
        COASetup,
        COASetup[SubHeaderID] IN { 1221, 1222, 1223, 1224, 1225 }
    )
VAR RepValue =
    CALCULATE ( - [Amount (dbcr balance)], dbamount, TermTable )
VAR ExclCurrentYrAvg =
    AVERAGEX (
        FILTER ( VALUES ( 'CALENDAR'[Year] ), 'CALENDAR'[Year] < YEAR ( TODAY () ) ),
        CALCULATE ( [Amount (dbcr balance)] + 0, DbAmount, TermTable )
    )
VAR AnnualValue =
    CALCULATE ( [Amount (dbcr balance)]+0, dbamount, TermTable )
VAR ReportValue =
    IF ( HASONEVALUE ( 'CALENDAR'[Year] ), AnnualValue, ExclCurrentYrAvg )
RETURN
    CALCULATE ( - ReportValue )

[PaulDBrown · Accepted solution]

@Dellis81 

In my experience, Iterators within VAR do not return expected results (since as a VAR the result is stored in memory?)

I suggest you break the measure down to make the iterator a result, and then build the following steps using the result of the iterator measure.

[Dellis81]

Thank you Paul for your response.    I would agree, unexpected results!

I did take your advice and split into numerous separate measures - and still had problems.  But, eventually realized, those years with a blank value - were excluded from the average.   I knew of this issue, and was able to resolve.   Then, I went back in to the main measure and "consolidated" a few of the "sub measures" into var, but found there was one certain sub measure that I had to leave as a measure.  Going the var route did not work.    So, alot of things I do not completely understand, thanks for encouraging to split into measures - and then I worked backwards from there. 

thank you