Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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?
Solved! Go to 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.
@parry2k Thank you.
I've attached a sample file with what i described on the first post
https://wetransfer.com/downloads/b94ea72acfcd0a9d88d69c0b77526be020201217173718/277ef3
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.
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!
@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.