cancel
Showing results for
Did you mean:
sclencioco Regular Visitor

## sum of values with different currency

Hello,

We have to compute for the grand total amount, however, the data contains two different currencies we have a list of values to convert USD to PHP (exchange_rate_id is 1) but we need to capture the latest conversion per month can I ask for help for the formula. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions v-cherch-msft
Microsoft

## Re: sum of values with different currency

You may add a month column for your rate table. Then link the Rate table with the Amount Table in relationship view. Then you may create a measure such as:

```Convert =
CALCULATE (
SUM ( Amount[Amount] ) * MAX ( Rates[LatestRate] ),
FILTER ( ALL ( Amount ), Amount[Month] = MAX ( Amount[Month] ) )
)``` Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6 v-cherch-msft
Microsoft

## Re: sum of values with different currency

You may get the latest conversion per month by below calculated column or measure:

```LatestRate =
VAR RankForDate =
RANKX (
FILTER (
Rates,
YEAR ( Rates[covered_date] ) = YEAR ( EARLIER ( Rates[covered_date] ) )
&& MONTH ( Rates[covered_date] ) = MONTH ( EARLIER ( Rates[covered_date] ) )
),
Rates[covered_date],
,
DESC,
DENSE
)
RETURN
IF ( Rates[exchange_rate_id] = 1 && RankForDate = 1, Rates[rate] )``` `Measure =VAR RankForDate = RANKX ( FILTER ( ALL ( Rates ), YEAR ( Rates[covered_date] ) = YEAR ( MAX ( Rates[covered_date] ) ) && MONTH ( Rates[covered_date] ) = MONTH ( MAX ( Rates[covered_date] ) ) ), CALCULATE ( MAX ( Rates[covered_date] ) ), , DESC, DENSE )RETURN IF ( MAX ( Rates[exchange_rate_id] ) = 1 && RankForDate = 1, MAX ( Rates[rate] ) )`

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sclencioco Regular Visitor

## Re: sum of values with different currency

Hello,

I was able to get the latest per month, however, how can I get the converted amount in PHP? here's the table of conversion Below is sample amount (in USD) to be converted to PHP  v-cherch-msft
Microsoft

## Re: sum of values with different currency

You may add a month column for your rate table. Then link the Rate table with the Amount Table in relationship view. Then you may create a measure such as:

```Convert =
CALCULATE (
SUM ( Amount[Amount] ) * MAX ( Rates[LatestRate] ),
FILTER ( ALL ( Amount ), Amount[Month] = MAX ( Amount[Month] ) )
)``` Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
sclencioco Regular Visitor

## Re: sum of values with different currency

Hello,

I Experienced this error when I try to create relationship  v-cherch-msft
Microsoft

## Re: sum of values with different currency

It seems you need to update the version. Then set the relationship. Here is the sample file for your reference.

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
oslosa Frequent Visitor

## Re: sum of values with different currency

Hi, there are multiple solutions to the currency conversion problem online, but none seem to get the sumtotal right, including this solution. Do you know how to solve it? I suppose it would contain some combinations of IF(hasonevalue()) and sumx.

Announcements #### New Topics Started Badges Coming  