cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sclencioco Regular Visitor
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

 

wvefwfcssddvs.JPG

 

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

 

changerates.JPG

 

can I ask for help for the formula. Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-cherch-msft
Microsoft

Re: sum of values with different currency

Hi @sclencioco

 

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] ) )
)

1.png

 

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.

View solution in original post

6 REPLIES 6
Microsoft v-cherch-msft
Microsoft

Re: sum of values with different currency

Hi @sclencioco

 

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] )

1.png

 

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
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?

NO1.JPG

 

here's the table of conversion

NO2.JPG

 

 

 

Below is sample amount (in USD) to be converted to PHP

 

NO3.JPG

 

Microsoft v-cherch-msft
Microsoft

Re: sum of values with different currency

Hi @sclencioco

 

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] ) )
)

1.png

 

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.

View solution in original post

sclencioco Regular Visitor
Regular Visitor

Re: sum of values with different currency

Hello,

 

I Experienced this error when I try to create relationship

 

relationship.JPG

Microsoft v-cherch-msft
Microsoft

Re: sum of values with different currency

Hi @sclencioco

 

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)