Multiply values in visual according to slicer selection (Currency slicer)

[Anonymous]

Hi, this is my first message on this forum.

As stated in the title, I need to create a visual that works with a currency slicer. When a currency is selected from the slicer, it looks up the correct exchange rate and multiplies the USD amount in the visual with that exchange rate for a successful currency conversion.

Data relevant for this task:

- 'CurrenciesDim' containing [RateUSD] with the exchange rate from USD and [Currency] acting as an ID.

- 'Invoices' containing [Amount] (currency is USD by default) and [InvoiceID]

'Invoices' *:1 'CurrenciesDim'

I've created the following measures for currency filtering

[AllAmount]: a sum of 'Invoices'[Amount])

[SelectedCurrencyValue]: used to bring up the exchange rate selected in a slicer, and if nothing is selected, it's USD)

SelectedCurrencyValue = IF(
HASONEFILTER('CurrenciesDim'[Currency]),
LOOKUPVALUE('CurrenciesDim'[RateUSD],'CurrenciesDim'[Currency],
VALUES('CurrenciesDim'[Currency])
),
LOOKUPVALUE('CurrenciesDim'[RateUSD],'CurrenciesDim'[Currency],"USD")
)

As well as [Relative], which is supposed to be an amount that is in accordance with the currency selection:

Relative = SUMX('Invoices',[All Amount]*[SelectedCurrencyValue])

However, when I create a visual showing the amount in a selected currency, the visual only works when the slicer selection is "USD" or none.

If I select some other currency, the visual goes blank.

I would appreciate any help or ideas 😄 Thank you in advance, love from Finland!

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, the [Currency] for slicer will affect your [AllAmount] measure since there is a relationship between the two tables:

So I suggest you delete the relatiohship.

Then try the following formula:

AllAmount = SUM(Invoices[Amount])

SelectedCurrencyValue = 
var _curr=IF(ISFILTERED('CurrenciesDim'[Currency]),MAX('CurrenciesDim'[Currency]),"USD")
return CALCULATE(MAX('CurrenciesDim'[RateUSD]),FILTER('CurrenciesDim','CurrenciesDim'[Currency]=_curr))

//Or
//IF(ISFILTERED('CurrenciesDim'[Currency]),MAX('CurrenciesDim'[RateUSD]),1)

Relative = [SelectedCurrencyValue]*[AllAmount]

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Evelyn,

It works exactly like you demonstrated. Thanks a lot for the help, this issue has been bugging me for quite some time now. Have a nice day!