How To Unify the Report Currency Based On the Slicer

[Shoukry]

Hi I have a problem to Unify the Report currency based on the selected currency by the user because each line in the transaction table will include different currency

I have applied a sample of my case in the below image, I have two tables,

1-The Exchange rate table, that includes cross-mapping between all currencies
2-The Revenue Transactions table, that includes the revenue amount for each company knowing that each company has different currency

[Ashish_Mathur]

Hi,

So what is your expected result?  Which currency do you want all revenue figures to get converted to?  Are you expecting another column in Table2?  Please clarify.

[Shoukry]

Hi

thanks for your reply,

the end user will need to show the report in {CHF or USD or OMR} through the slicer, in this case, I want to convert all amounts that in the transaction table based on the user selection, 

which mean if the user selects to show the report in CHF, I will need to convert the base currency for each company to CHF,  knowing that each company in the transaction table has deferent currency 

[Shoukry]

Hi
thanks for your reply,

the end user will need to show the report in {CHF or USD or OMR} through the slicer, in this case, I want to convert all amounts that in the transaction table based on the user selection,

which mean if the user selects to show the report in CHF, I will need to convert the base currency for each company to CHF, knowing that each company in the transaction table has a specific currency , so the currency rate will be variable on each line based on the company currency

[Zubair_Muhammad · Accepted solution]

Hi @Shoukry

See the attached file.

Hope it helps

Assuming the User Slicer is from Foregin Currency in Table1, we can pull the relevant exchange rate in Table 2 for the same month and same year using this

Applicable Exchange Rate =
CALCULATE (
    VALUES ( Table1[Ex Rate] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[Foreign Currency] ),
        Table1[Base Currency] = SELECTEDVALUE ( Table2[Base Currency] )
            && Table1[Month] = SELECTEDVALUE ( Table2[Month] )
            && Table1[YEAR] = SELECTEDVALUE ( Table2[YEAR] )
    )
)

Then Revenue in the selected Currency is simply

Revenue in Selected Currency =
[Applicable Exchange Rate] * SELECTEDVALUE ( Table2[Revenue] )

[Shoukry]

Hi Zubair

Many thanks for your wonderful solution, the case has been solved

Thanks a lot.

[Anonymous]

Hi,

I tried this solution and was great, however it doesnt work when i want to aggregate values for a category, for example new customers and old customers, it only shows values when on indvidual customer / transaction level.

Do you have any tips for solving this?