Dynamic Currency Conversion Incorrect Summary for Measure

[nssidhu74]

I am running into Incorrect Summation problem while converting Currency as show here. 

Also few other problem that i run into.
1)How do i prevent Summary in Exchange Rate Column(It is also displaying incorrect Sum).?
2) Is there a way to Update Header of Column based on the Slicer Value ?

Here are the Measure Formulas that i am using.

ExchangeRateForSelectedCurrency = SUMX(Sales, LOOKUPVALUE(ExchangeRate[Rate],

ExchangeRate[CurrencyFrom],SELECTEDVALUE(Sales[UnitOfCurrency])

,ExchangeRate[CurrencyTo],SELECTEDVALUE(Currencies[UnitOfCurrency])

,ExchangeRate[Date],SELECTEDVALUE(Sales[DateOfSale]),1

))

Converted Value based on Slicer(Measure)

Calculated Value = SUMX(Sales, Sales[Cost] * LOOKUPVALUE(ExchangeRate[Rate],

ExchangeRate[CurrencyFrom],SELECTEDVALUE(Sales[UnitOfCurrency])

,ExchangeRate[CurrencyTo],SELECTEDVALUE(Currencies[UnitOfCurrency])

,ExchangeRate[Date],SELECTEDVALUE(Sales[DateOfSale]),1

))

Here is the Data that i have used.

CurrencyFrom	CurrencyTo	Rate	Date	ToCurrency Symbol
USD	EURO	0.9	12/20/2019	$ USD
USD	POUND	0.76	12/20/2019	$ USD
USD	INR	71.78	12/20/2019	$ USD
EURO	POUND	0.85	12/20/2019	€  EURO
EURO	INR	80.15	12/20/2019	€  EURO
EURO	USD	1.12	12/20/2019	€  EURO
POUND	USD	1.131	12/20/2019	£ POUND
POUND	EURO	1.17	12/20/2019	£ POUND
POUND	INR	93.86	12/20/2019	£ POUND
INR	USD	0.014	12/20/2019	₹ Rupee
INR	EURO	0.012	12/20/2019	₹ Rupee
INR	POND	0.011	12/20/2019	₹ Rupee
USD	EURO	0.95	12/28/2019
USD	POUND	0.71	12/28/2019
USD	INR	74.78	12/28/2019
EURO	POUND	0.8	12/29/2019
EURO	INR	82.15	12/29/2019
EURO	USD	1.22	12/29/2019
EURO	POUND	0.8	12/30/2019
EURO	INR	82.15	12/30/2019
EURO	USD	1.22	12/30/2019
POUND	USD	1.331	12/31/2019
POUND	EURO	1.27	12/31/2019
POUND	INR	95.86	12/31/2019
USD	EURO	0.9	12/25/2019
USD	POUND	0.76	12/25/2019
USD	INR	71.78	12/25/2019
EURO	POUND	0.85	12/25/2019
EURO	INR	80.15	12/25/2019
EURO	USD	1.12	12/25/2019

Sales Data.

ItemID	Cost	QTY	UnitOfCurrency	DateOfSale
Item1	30	2	USD	12/20/2019
Item2	45	5	USD	12/20/2019
Item3	70	3	USD	12/20/2019
Item4	45	2	EURO	12/20/2019
Item5	30	6	POUND	12/20/2019
Item6	10	2	POUND	12/20/2019
Item7	230	2	EURO	12/25/2019
Item8	2	8	USD	12/25/2019
Item9	67	4	INR	12/20/2019
Item10	23	2	USD	12/28/2019
Item11	56	1	EURO	12/29/2019
Item12	23	3	EURO	12/30/2019
Item13	56	6	POUND	12/31/2019

[mwegener · Accepted solution]

Hi @nssidhu74 

try this measures.

ExchangeRateForSelectedCurrency = 
IF (
    HASONEVALUE ( Sales[UnitOfCurrency] ),
    SUMX (
        Sales,
        LOOKUPVALUE (
            ExchangeRate[Rate],
            ExchangeRate[CurrencyFrom], Sales[UnitOfCurrency],
            ExchangeRate[CurrencyTo], SELECTEDVALUE ( Currencies[UnitOfCurrency] ),
            ExchangeRate[Date], Sales[DateOfSale],
            1
        )
    ),
    BLANK ()
)

Calculated Value = 
SUMX (
    Sales,
    Sales[Cost]
        * LOOKUPVALUE (
            ExchangeRate[Rate],
            ExchangeRate[CurrencyFrom], Sales[UnitOfCurrency],
            ExchangeRate[CurrencyTo], SELECTEDVALUE ( Currencies[UnitOfCurrency] ),
            ExchangeRate[Date], Sales[DateOfSale],
            1
        )
)

A conditional column header is actual not possible.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.