Forum Discussion
Cuurncy conversion
Hi I have a query regarding currency conversion.
I have 5-6 POS( Point of sales) CA, US, AU, NZ.
I have a slicer Of POS when I select any single one then it is showing respective Cuurncy sales, but on multiple selection of POS(AU, CA, US, NZ) it is merging all CAD, USD, AUD etc. I need to convert them in one wether in CAD or in USD.
Please suggest any measure.
Thanks in advance
Gurpreet Singh
lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi Gurpreetsingh1 ,Here are the steps you can follow：
1. Create a table that contains the exchange rates for each currency relative to your desired base currency (e.g., USD or CAD).
2. Create measure.
USRate = var _rate=MINX(FILTER(ALL('RateTable'),'RateTable'[CurrencyA]=MAX('Table'[Point])&&[Convert CurrencyB]="US"),[Conversion rate]) return SUMX( FILTER(ALL('Table'),'Table'[Point]=MAX('Table'[Point])),[Sales]) * _rateCARate = var _rate=MINX(FILTER(ALL('RateTable'),'RateTable'[CurrencyA]=MAX('Table'[Point])&&[Convert CurrencyB]="CA"),[Conversion rate]) return SUMX( FILTER(ALL('Table'),'Table'[Point]=MAX('Table'[Point])),[Sales]) * _rate3. Result:
If the results above don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi Gurpreetsingh1 ,Here are the steps you can follow：
1. Create a table that contains the exchange rates for each currency relative to your desired base currency (e.g., USD or CAD).
2. Create measure.
USRate = var _rate=MINX(FILTER(ALL('RateTable'),'RateTable'[CurrencyA]=MAX('Table'[Point])&&[Convert CurrencyB]="US"),[Conversion rate]) return SUMX( FILTER(ALL('Table'),'Table'[Point]=MAX('Table'[Point])),[Sales]) * _rateCARate = var _rate=MINX(FILTER(ALL('RateTable'),'RateTable'[CurrencyA]=MAX('Table'[Point])&&[Convert CurrencyB]="CA"),[Conversion rate]) return SUMX( FILTER(ALL('Table'),'Table'[Point]=MAX('Table'[Point])),[Sales]) * _rate3. Result:
If the results above don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks for the solution
