Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
ifenek
Frequent Visitor

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? 

 

ifenek_0-1608210312579.png

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

1.png

Select USD.

2.png

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. 

 

View solution in original post

4 REPLIES 4
ifenek
Frequent Visitor

@parry2k Thank you. 

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

 

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

 

 

 

Anonymous
Not applicable

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.

1.png

Select USD.

2.png

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. 

 

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! 

 

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.