cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dynamic Rate of Exchange Calculation

Hi all,

I'm relatively new to Power BI (Really like the product), however I wondered if I could get some help regarding a dynamic calculation.

I have 2 data sources "Amt" and "RoE":

Amt

 CCY Amount GBP 10,000 USD 20,000 CAD 30,000 EUR 40,000

RoE

 Rate Type CCY RoE PLAN CAD 1.96 PLAN EUR 1.41 PLAN USD 1.57 PLAN GBP 1 AVERAGE CAD 1.84 AVERAGE EUR 1.25 AVERAGE GBP 1 AVERAGE USD 1.39 GQD CAD 1.71 GQD EUR 1.18 GQD GBP 1 GQD USD 1.31 QTREND CAD 1.71 QTREND EUR 1.16 QTREND GBP 1 QTREND USD 1.3

Depending on the Rate Type selected by the user on a slicer, it would divide the "Amount" in the table "Amt" by "RoE" in the "RoE" table.

Below are the results I'm getting:

 CCY Amt RoE Amt-GBP CAD 30,000 1.96 15,306 EUR 40,000 1.41 28,369 GBP 10,000 1 10,000 USD 20,000 1.57 12,739 Total 100,000 5.94 16,835

The new measure I created "Amt-GBP" works fine for the individual rows.

However in the example above, the Total for "Amt-GBP" I would expect to be sum of the "Amt-GBP" column being 66,414.

But the total is a calculation "Amt" (100,000) divisde by the total of "RoE" (5.94).

How can I get the total to be 66,414?

Hopefully this is clear.

Jason

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Dynamic Rate of Exchange Calculation

@jasgun

You can try with following measure formula.

```Amt-GBP =
SUMX ( Amt, CALCULATE ( SUM ( Amt[Amount] ) ) / CALCULATE ( SUM ( RoE[RoE] ) ) )```

Best Regards,

Herbert

3 REPLIES 3
Skilled Sharer

## Re: Dynamic Rate of Exchange Calculation

Hi Jason,

You will need to define a different calculation for the total row, you can do this with an if statement and the hasonevalue function to tell PBI what to do in each case, in the example below if there is only one value in CCY field it will use your Amt-GBP measure, if not it will use a new measure.

```if(hasonevalue(CCY-col),
Amt-GBP,
new-measure
)```

To get the sum of the sums you can use the sumx function in the new measure, something like

`sumx(ROE,[Amt-GBP])`

That should calcuate Amt-GBP for each row then add those totals up

Microsoft

## Re: Dynamic Rate of Exchange Calculation

@jasgun

You can try with following measure formula.

```Amt-GBP =
SUMX ( Amt, CALCULATE ( SUM ( Amt[Amount] ) ) / CALCULATE ( SUM ( RoE[RoE] ) ) )```

Best Regards,

Herbert

Frequent Visitor

## Re: Dynamic Rate of Exchange Calculation

Perfect, thank you!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors