Multiply values from two related tables based on the input of a slicer

[garethterb]

Hi all I am new to PowerBI and am having trouble with a matrix table.

I am trying to convert the amounts in table based on the currency selected in a slicer i.e.

Table A has 5 colomns namely: Currency (eg USD), Period (eg 201908) Amount (eg 100) and Combine (eg USD201908)

Table B has 5 colomns, namely: CurrencyFrom (eg USD), CurrencyTo(eg EUR), Rate (eg 0.89), Period (eg 201908) and Combine (USD201908).  Table B will have multiple recordes where Currency from is USD but CurrencyTo is different with t a different rate. I then have a slicer that if I select EUR I want output from Table A to be  Amount*Rate (100*0.89). I have tried variaus formulas in measure but keep getting errors.

Any ideas?

[garethterb]

I have managed to find a solution to this problem by creating a new Quick Measure on the CurrencyTo filter. The only issue now is that the Colomn subtotal does not add up correctly when there are multiple couomns each with a different base currency e.g. Column 1 = EUR958.47 EUR and column 2 GBP13542.71 when converted to USD the values are 1066.72 and 16484.86 but the subtotal on the column is shown as 33790.50.

[v-shex-msft · Accepted solution]

HI @garethterb ,

This is a common calculation issue on measure total level, you can take a look at following blog about measure total calculation to fix it:

Measure Totals, The Final Word

If above link not help, please share some sample data and expected result to test.

Regards,
Xiaoxin sheng

[garethterb]

Thanks you for this information it is very helpful and has allowed me to do what I need. I must however comment that this has had a considerable effect on the speed that the matrix recalculates when filters are changed. Its also a bit disappointing that these kinds of work arounds need to be done as one would expect a straight forward column total to add up the displayed values and not display seemingly random numbers.