Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dyabes
Helper I
Helper I

Converting a calculated column into measure but issue with iteration

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 )

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@dyabes , 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 ) )

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@dyabes , 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 ) )

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.