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

CCYAmount
GBP10,000
USD20,000
CAD30,000
EUR40,000

 

RoE

Rate TypeCCYRoE
PLANCAD1.96
PLANEUR1.41
PLANUSD1.57
PLANGBP1
AVERAGECAD1.84
AVERAGEEUR1.25
AVERAGEGBP1
AVERAGEUSD1.39
GQDCAD1.71
GQDEUR1.18
GQDGBP1
GQDUSD1.31
QTRENDCAD1.71
QTRENDEUR1.16
QTRENDGBP1
QTRENDUSD1.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:

 

CCYAmtRoEAmt-GBP
CAD30,0001.9615,306
EUR40,0001.4128,369
GBP10,000110,000
USD20,0001.5712,739
Total100,0005.9416,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.

Many thanks in advanced.

 

Jason

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
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] ) ) )

Dynamic Rate of Exchange Calculation_1.jpg

 

Best Regards,

Herbert

View solution in original post

3 REPLIES 3
itchyeyeballs Skilled Sharer
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
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] ) ) )

Dynamic Rate of Exchange Calculation_1.jpg

 

Best Regards,

Herbert

View solution in original post

jasgun
Frequent Visitor

Re: Dynamic Rate of Exchange Calculation

Perfect, thank you!

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors