Power BI calculate sum only one value of duplicate Iinvoice lines and dynamically convert currency

Question

I have the below data, a list of detailed invoice product lines. Each invoice has an estimated total amount which appears on several lines when the invoice has more than 1 product.

SUM of invoice lines with dynamic exchange currency PBIX 

What I want to achieve is to calculate the Estimated Revenue (in selected currency) for each individual invoice.

Invoice 1 has  TotalEstimated = 500 but when I convert it to USD (FX rate=2 ) it should be 1000 not 3000 like below.

The result is 3000 because there are 3 prooducts on invoice 1 and it adds the Estimated Invoice Total for all the lines. 500*3=1500 converted to USD equals 3000.

Sum and conversion is not working

Invoice Details table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkMDAyBpCiad8/NSQMK5iVX5eUCGgZG+gam+kYGRkVKsDkSLExAbkabFD4iNidVghGIHhAxOzEtE1mJoqm9gAtJiCNYCMtsZbr4Zdi2W+gZmCFtAWlzAJhGtxQQo5Ap2EowIyS9KRtFgAtUAdFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Product = _t, Price = _t, #"Estimated Invoice total" = _t, Customer = _t, Details = _t, #"Invoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"Product", type text}, {"Price", Int64.Type}, {"Estimated Invoice total", Int64.Type}, {"Customer", type text}, {"Details", type text}, {"Invoice Date", type date}})
in
    #"Changed Type"

Measures Invoice Details table

Actual Price (Base currency) = SUM('Invoice Details'[Price])

Actual Price (in selected currency) = 
SUMX( 
'Invoice Details', 
 VAR vExchange =
           LOOKUPVALUE(
                'Currency Rates'[exchangerate],
                'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
                'Currency Rates'[Year], 'Invoice Details'[Invoice Date].[Year]
                ) 
RETURN 
'Invoice Details'[Actual Price (Base currency)]*vExchange
 )

TotalEstimated = SUMX ( SUMMARIZE ('Invoice Details', [Invoice], "Result", AVERAGE ( 'Invoice Details'[Estimated Invoice total] ) ), [Result] )

Currency Rates Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUTIyMLQEUq6lRfkgKjRIKVYHRSY0WMElPycnsQjMdgFLm4CljQwwNSLJYNNoDJE2xNSIJINNoxFE2ghTI5IMhsZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [exchangerate = _t, year = _t, currency_name = _t, currency_code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"exchangerate", type number}, {"year", Int64.Type}, {"currency_name", type text}, {"currency_code", type text}})
in
    #"Changed Type"

Currency Filter Table

let
    Source = #"Currency Rates",
    #"Removed Other Columns" = Table.SelectColumns(Source,{"currency_code"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

Measures Currency Filter Table

Selected Currency = SELECTEDVALUE('Currency Filter'[currency_code])

Thank you

I have tried to calculate a dynamic exchange when the user selects a currency, but could not work it out.

Estimated Revenue (in selected currency) = 
SUMX( 
'Invoice Details', 
 VAR vExchange =
           LOOKUPVALUE(
                'Currency Rates'[exchangerate],
                'Currency Rates'[currency_code], 'Currency Filter'[Selected Currency],
                'Currency Rates'[Year], 'Invoice Details'[Invoice Date].[Year]
                ) 
RETURN 
'Invoice Details'[TotalEstimated]*vExchange
 )

Answer 1

This problem can be solved by relastionships, to do so you need to add an ID (as a calculated column) in both Invoice Details and Currency Rates tables, let's call it CurrencyID:

Currency Rates:

CurrencyID = CONCATENATE("SEK",'Currency Rates'[year])

Invoice Details:

CurrencyID = CONCATENATE("SEK",'Invoice Details'[Invoice Date].[Year])

After that we create a Many-to-Many relationship based on these 2 columns.

And finally the measure Estimated Revenue (in selected currency) will change to:

Estimated Revenue (in selected currency) = CALCULATE([TotalEstimated] * MAX('Currency Rates'[exchangerate]))