cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leoams
Regular Visitor

Conversion of values ​​by exchange rate

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.

leoams_1-1647021180221.png

 

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:

 

leoams_2-1647021751517.png

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

3 REPLIES 3
tamerj1
Community Champion
Community Champion

Hi @leoams 
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 1column 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.
1.png2.png

Whitewater100
Solution Sage
Solution Sage

Hola:

 

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

 

Whitewater100_0-1647026151333.png

 

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 😞 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors