Currency Filtering

[Anonymous]

Hello Everyone,

I was searching forum, but didn't find a solution to my issue. 

I have a table "BDR Dataset" in Power BI Desktop that contains multiple companies, therefore multiple currencies. Each company has its own, local currency. Data is presenting the invoice amounts.

What I want to do is to translate these currencies into USD. I have a separate table with FX Rates ("BudRate") and I have created a column in "BDR Dataset" that is doing the lookupvalue formula (i.e. find the correct currency for the company, take the exchange rate to USD and multiply by invoice amount). No issues here.

My problem is that in the dashboard view I want to be able to filter (slicer) if I want to see value in USD or in local currency. How can I do that?

Thanks for help!

[Anonymous]

Hi @Anonymous ,

I think there are some steps you need to do:

1.create a new column in table for USD Amount, then you will have 2 columns, one for local amount, one for USD amount.

2.create a table with one column named "currency code" which have 2 values, Local and USD

3.create a measure, like : if(max(currency code) = 'Local', sum(your_local_amount),sum(your_USD_amount_column))

Please try it.

Aiolos Zhao

[Anonymous]

@Anonymous thank you for quick response!

1. Done

2. Could you please explain further? Something like this?

-----------------

Currency Code

-----------------

Local

USD

[Anonymous]

Ok I guess it was not the best solution, but I did some workaround.

- I have a slicer that has two options (local currency or usd)

- I have a measure that is showing either the USD FX for local currency or 1 if selected local currency (depending which option we choose in the above-mentioned slicer)

- I have created a new column that is supposed to multiply invoice amount by this measure, but for some reason, it is not working...

does anyone have any idea why?

[Anonymous]

Hi @Anonymous ,

I created a sample data, and I assumed that you have below data:

And the measure calculates the USD amount,

so after you have that slicer(Local / USD),

you can create a new measure like:

if(max(slicer) = 'Local', sum(amount), my_measure)

Please try.

Aiolos Zhao

[Anonymous]

@Anonymous - thank you for trying to help, appreciate that.

However, I don't think I follow. Below is the sample data. Maybe this would help?

The issue I have now that calculated column "Inv_Amount_Calculated_FX" is not multiplied by a measure "FX_Rate".

Measure "FX_Rate" depends on Entity selected in a slicer "Entity" and depends on a choice in slicer "Select_Currency".

My expected result would be that values in a column "Inv_Amount_Calculated_FX" are multiplied by measure "FX_Rate".

thanks a lot!

Dataset_01

Dataset_02

Dataset_03

[Anonymous]

Hi @Anonymous ,

I create a sample using you sample data, and I change your calcuated column to measure, it works:

New Amount = SUM('Table'[Amount]) * [FX_Rate]

Aiolos Zhao