Need help dealing with multiple currency and converting them

[ifenek]

Hello, 

I can't get my head around on how to do this so i'm coming here for help. 

I have a column homenett holding all my values. The problem they come from differente databases and therefore different currency. I also have a column CurrencyID holding all my currency used. 

I want the user to choose the currency in which the report gonna show itself. 

I've build up a table with all the exchange rate my company is using as base being GBP. So far i've tried few things but no real success.

I thought doing something like converting first everything in Gpb and then creating mesures to put in whatever is selected in the slicer? 

[ifenek]

@parry2k Thank you. 

I've attached a sample file with what i described on the first post

https://wetransfer.com/downloads/b94ea72acfcd0a9d88d69c0b77526be020201217173718/277ef3

[v-rzhou-msft · Accepted solution]

Hi @ifenek 

I think your currid: CAN in VW_Currency Table should be CAD, or we can't find CAD in VW_Currency Table.

You can achieve your goal by measure.

Measure:

Measure = 
VAR _SelectID =
    SELECTEDVALUE ( VW_Currency[currid] )
VAR _PerRate =
    SUMX (
        FILTER (
            ALL ( VW_Currency ),
            VW_Currency[currid] = MAX ( VW_Orders[CurrencyID] )
        ),
        VW_Currency[currate]
    )
VAR _SelRate =
    SUMX (
        FILTER ( ALL ( VW_Currency ), VW_Currency[currid] = _SelectID ),
        VW_Currency[currate]
    )
VAR _GBPhomenett =
    DIVIDE ( SUM ( VW_Orders[homenett] ), _PerRate )
VAR _Selhomenett =
    DIVIDE ( SUM ( VW_Orders[homenett] ), _PerRate ) * _SelRate
RETURN
    IF ( ISFILTERED ( VW_Currency[curdesc] ), _Selhomenett, _GBPhomenett )

M.homenett = SUMX(VW_Orders,[Measure])

Build a measure to show the currency format.

Currency = 
IF(ISFILTERED(VW_Currency[curdesc]),SELECTEDVALUE(VW_Currency[curdesc]),"GBP")

Result is as below.

By default if the slicer is unfiltered, it will show homenett in GBP.

Select USD.

You can download the pbix file from this link: Sample

Best Regards,

Rico Zhou

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

[ifenek]

Hi, @v-rzhou-msft Thank you for you reply And apology for late reply. 

It looks like your solution fits my needs. I'll have a look on your file! 

Thank you a lot for your help and happy new year! 

[parry2k]

@ifenek I would recommend sharing a sample pbix file and will get you the solution. 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡