Converting a calculated column into measure but issue with iteration

[Anonymous]

I have this DAX as a calculated column that calcuates the NetRevenue per row. There are two adjustments that needs to be done which are Currency Exchange Rate (since not all Orders are on USD) and CreditRate (assumption on how many orders will be returned).

The Currency Exchange Rate is based on a table with values per day for AUD, GBP and EUR.

The Credit Rate is only applied to orders in the current fiscal year. 

Everything is working with no issues but in pursue of a slight efficiency gain and learning I've been trying to convert this calculated column into a Measure. Unfortunately I've been stuck with an iteration issue and cannot find the correct approach. Can someone help me and point me to the right direction?

Thanks in advance!

Cheers,

David

NetRevenue = 
VAR RevenueAfterTax = 'Order Header'[TotalPaymentReceived] - 'Order Header'[TotalTax]
VAR ExchangeRate =
    LOOKUPVALUE (
        'Currency Exchange'[Rate],
        'Currency Exchange'[Date], 'Order Header'[OrderDate],
        'Currency Exchange'[Exchange], SWITCH (
            TRUE (),
            'Order Header'[Currency] = "AUD", "AUD_to_USD",
            'Order Header'[Currency] = "GBP", "GBP_to_USD",
            'Order Header'[Currency] = "EUR", "EUR_to_USD"
        ),
        1
    )
VAR CreditRate =
    IF (
        RELATED ( 'Calendar'[Year Fiscal] ) IN {"FY 2022"},    
        LOOKUPVALUE (
            'Sales Adjustments'[Credit Rate],
            'Sales Adjustments'[Brand Code], 'Order Header'[Brand]
        ),
        0
    )
RETURN
    ( RevenueAfterTax * ExchangeRate ) * ( 1 - CreditRate )

[amitchandak · Accepted solution]

@Anonymous , Try to move this calc to expression part of sumx

NetRevenue =
Sumx('Order Header',

VAR RevenueAfterTax = 'Order Header'[TotalPaymentReceived] - 'Order Header'[TotalTax]
VAR ExchangeRate =
LOOKUPVALUE (
'Currency Exchange'[Rate],
'Currency Exchange'[Date], 'Order Header'[OrderDate],
'Currency Exchange'[Exchange], SWITCH (
TRUE (),
'Order Header'[Currency] = "AUD", "AUD_to_USD",
'Order Header'[Currency] = "GBP", "GBP_to_USD",
'Order Header'[Currency] = "EUR", "EUR_to_USD"
),
1
)
VAR CreditRate =
IF (
RELATED ( 'Calendar'[Year Fiscal] ) IN {"FY 2022"},
LOOKUPVALUE (
'Sales Adjustments'[Credit Rate],
'Sales Adjustments'[Brand Code], 'Order Header'[Brand]
),
0
)
RETURN
( RevenueAfterTax * ExchangeRate ) * ( 1 - CreditRate ) )

[Anonymous]

That worked flawlessly. I guess I was evaluating SUMX in the wrong place. Thank you!