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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GraceTCL
Helper II
Helper II

Conversion error

Hi,

 

I am facing error converting from Local Currency (LC) to functional USD currency. There are null in my column of values.

 

Currently, this is how I have linked up my tables where the "Date_ID" connecting keys are in Integer format.

 

"Food Metrics" table is linked to d.merchant table where the Vertical and subvertical are look-uped against.

This is how I have connected my data:

connect.PNG

My formula as such:

 

1. GMV LC = COALESCE(sum(FoodMetrics[GMV_LC]),0)

2. GMV USD = divide([GMV LC],[Currency rate])

3. 

Currency rate =
calculate(
SELECTEDVALUE('Exchange Rate'[exchange_one_usd]),
Filter(
'Exchange Rate',
min(FoodMetrics[date_id])>='Exchange Rate'[start_date_id]
&& max(FoodMetrics[date_id])<='Exchange Rate'[end_date_id]
)
)
 

Few issues:

1. I am not sure why when I pull the data by "date_ID" the USD number shows up but it is not summed across the periods. See first picture.

2. When I pull by "vertical" view, only LC number is showing up. See second picture.

 

1st pic:

Output 1.PNG

2nd pic:

Output 2.PNG

 

 

 
1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@GraceTCL  In the first table where you use Date_ID, Power BI does not sum the currency rate, it just provides the single value based on the DAX filter you've written. 

 

In the second table where you use Vertical, since there's no date, the SELECTEDVALUE('Exchange Rate'[exchange_one_usd]) part of your function has more than one value, so the way SELECTEDVALUE works, it just returns BLANK instead. 

 

What exchange rate would you like to return? The highest? Average?

 

Try: 

 

calculate(
AVERAGEX(FoodMetrics,
SELECTEDVALUE('Exchange Rate'[exchange_one_usd]),
Filter(
'Exchange Rate',
min(FoodMetrics[date_id])>='Exchange Rate'[start_date_id]
&& max(FoodMetrics[date_id])<='Exchange Rate'[end_date_id]
)
)
)
 
for example.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

Hi, @GraceTCL 

According to your description, if you want to sum the value up based on the context in your DAX formula, the Sumx() function can be useful to calculate it.

https://docs.microsoft.com/en-us/dax/sumx-function-dax

 

If you still have a problem, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

 

Best Regards,

Community Support Team _Robert Qin

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

AllisonKennedy
Super User
Super User

@GraceTCL  In the first table where you use Date_ID, Power BI does not sum the currency rate, it just provides the single value based on the DAX filter you've written. 

 

In the second table where you use Vertical, since there's no date, the SELECTEDVALUE('Exchange Rate'[exchange_one_usd]) part of your function has more than one value, so the way SELECTEDVALUE works, it just returns BLANK instead. 

 

What exchange rate would you like to return? The highest? Average?

 

Try: 

 

calculate(
AVERAGEX(FoodMetrics,
SELECTEDVALUE('Exchange Rate'[exchange_one_usd]),
Filter(
'Exchange Rate',
min(FoodMetrics[date_id])>='Exchange Rate'[start_date_id]
&& max(FoodMetrics[date_id])<='Exchange Rate'[end_date_id]
)
)
)
 
for example.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy 

 

My intention is to calculate the USD equivalent of the LC in every row and sums it up based on the context be it by date or by vertical. AverageX will not work. Is SumX the function to use or which is the correct formula? Pls advise. Thank you.

@GraceTCL  Yes, in this case SUMX will work for you, have you got that working yet or are you still stuck? Please advise where you're stuck, otherwise mark this post as solved so others can benefit. 🙂 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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