Forum Discussion
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.
Many thanks in advanced.
Jason
You can try with following measure formula.
Amt-GBP = SUMX ( Amt, CALCULATE ( SUM ( Amt[Amount] ) ) / CALCULATE ( SUM ( RoE[RoE] ) ) )
Best Regards,
Herbert
3 Replies
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
You can try with following measure formula.
Amt-GBP = SUMX ( Amt, CALCULATE ( SUM ( Amt[Amount] ) ) / CALCULATE ( SUM ( RoE[RoE] ) ) )
Best Regards,
Herbert
Perfect, thank you!
