Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Glen_
Regular Visitor

DAX currency translation

Hi,

 

I would be really greatful is someone could assist.  I'm very new to power BI and after searching a lot I'm still struggling for a solution here that suits my setup.

 

My data is all in one currency & I want to convert it into different versions of dollar rates.  The DAX query i pulled together worked okish when I had the same rate for each period (I dont have dates in the data, just numbered financial periods 1 to 12).

 

However some of my currency scenarios have a different rate per period and they pull in blank, so I need help changing this query to take into account the rates at different periods.

 

The second issue is in my value column I'm converting there is also volume that I do no want to convert and keep at the same value, this pulls from my P&L column in the data sheet

 

Current DAX formula:

 

FX adjusted value =
var value_ = CALCULATE(DATASHEETS[Sum of Value])
var ExchangeRate_ = selectedvalue('Currency'[Currency rate])
return
value_ * ExchangeRate_
 
Data sheet:
 
Glen__0-1666604297294.png

 

Currency sheet:

 

Glen__1-1666604393016.png

 

Also including relationships if this helps - the Period reference is what links them

 

Glen__2-1666604462347.png

 

Thanks for any assistance

1 ACCEPTED SOLUTION
Anonymous
Not applicable

ok i duplicated the values so i can have a similar scenario:
im still not sure if i understood correctly but here is my new table i sampled randome data numbers:

Screenshot 2022-10-24 153201.png

as you see  i have multiple gross profit on period 9 or 12 or whatever:

Screenshot 2022-10-24 153145.png

sum of values = CALCULATE(SUM(Daatasheet[Value]),ALL(Daatasheet[Value]))


now use the same formula but use:

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],1)<>"Volume",SUMX('Currency','Currency'[Currency rate] *[valueis]),SUM(Daatasheet[Value]))

View solution in original post

23 REPLIES 23

doesn't like valueis, in the formula - any ideas?

 

Glen__0-1666615491810.png

 

Anonymous
Not applicable

Oh my bad valueis is the measure of sum of values

Correct amount = IF(FIRSTNONBLANK(Daatasheet[P&L],1)<>"Volume",SUMX('Currency','Currency'[Currency rate] *[sum of values]),SUM(Daatasheet[Value]))
Anonymous
Not applicable

can you please show me the table before addin the measure? 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.