Conversion error

[GraceTCL]

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:

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:

2nd pic:

[v-robertq-msft]

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 · Accepted solution]

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

[GraceTCL]

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.

[AllisonKennedy]

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