topic Re: Conversion of values by exchange rate in DAX Commands and Tips

Conversion of values by exchange rate

Anonymous — Fri, 11 Mar 2022 18:14:08 GMT

Hi everyone!

I hope you are good and healthy.

I am facing some problems, that I supposed that is a DAX structuring problem.

I need to produce a P&L in BI.

I have some kinds of local currency that I need to transform by exchange rate per month in USD currency and BRL currency.

How you can see in example below, I don't know if my DAX is incorrect, because the conversion ir wrong.

Column 1(Total): Value in local currency

Column 2 (Taxa): is the exchange rate to transform local currency - to USD

Colum 3 (Taxa USD): is the value when divide TOTAL per TAXA.

Colum 4(Taxa BRL): i need take the column Taxa USD, sum all values and multiply to BRL exchange rate in that month.

The problems

1- The values is strange. When you do in calculator the yellow example.

Total: 49.604.600

Taxa: 5,15

So, the correct value for Taxa USD will be: 9.631.961, but in table the value is 9.634.647

All lines is doing the incorrect calculate.

DAX for TAXA USD:

Taxa USD = DIVIDE(SUM('Medidas Taxa'[Actual]),SUM('Medidas Taxa'[Taxa Soma]))

2- And in the subtotal "Revenue" for example. It isn't sum the values below, but it is doing the division between the total in bold. I know that it is wrong, but I don't know how to solve this.

3- In column 4 I need to sum all values for the column 3 and transform USD to BRL, using the exchange rate for that month.

DAX that I used:

Anyone face the same problem or can help me? Thank you!

The Power BI data is below:

https://drive.google.com/file/d/1g3ierhopwadSyhKC9baa9oCgOCD3MLaI/view?usp=sharing

Re: Conversion of values by exchange rate

Whitewater100 — Fri, 11 Mar 2022 19:16:35 GMT

Hola:

Maybe this missing value (Brz for March) is impacting results. It you test thru dates thru end of February, is it accurate?

Re: Conversion of values by exchange rate

tamerj1 — Sat, 12 Mar 2022 10:51:24 GMT

Hi Anonymous
I think the problem initiated originally from the data and the data model.
The dPlano de Contas HFM[HFM ACCOUNT] column is totally missing from the fTaxa Moeda Table therefore, the essential relationship between dPlano de Contas HFM and the fTaxa Moeda tables is missing and cannot be created. This is why in your visual the filter coming from dGrupo1[P&L Group 1] column does not reach to the fTaxa Moeda Table. YOu can see that clearly in attached screenshot.
While at the default currency level you can spot the effect of the filter coming from dGrupo Moeda[Default Currency] column because proper relation ship is created between the two tables. The same applies to dCalendar table where a proper relationship is created.

Re: Conversion of values by exchange rate

Anonymous — Mon, 14 Mar 2022 13:58:49 GMT

Hi Whitewater100,

Thank you for your support. I put the value in March, but this didn't solve the problem... I imagine that can be any relatioship, but I am not founding the problem 😞

topic Re: Conversion of values ​​by exchange rate in DAX Commands and Tips

Conversion of values ​​by exchange rate

Re: Conversion of values ​​by exchange rate

Re: Conversion of values ​​by exchange rate

Re: Conversion of values ​​by exchange rate

topic Re: Conversion of values by exchange rate in DAX Commands and Tips

Conversion of values by exchange rate

Re: Conversion of values by exchange rate

Re: Conversion of values by exchange rate

Re: Conversion of values by exchange rate