Dynamic Rate of Exchange Calculation

[jasgun]

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.

Many thanks in advanced.

Jason

[v-haibl-msft · Accepted solution]

@jasgun

You can try with following measure formula.

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

Best Regards,

Herbert

[jasgun]

Perfect, thank you!

[itchyeyeballs]

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