Forum Discussion

ifenek's avatar
ifenek
Frequent Visitor
5 years ago
Solved

Need help dealing with multiple currency and converting them

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? 

 

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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. 

     

4 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        ifenek
        Frequent Visitor
        5 years ago

        Hi, Anonymous 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!