trying to convert USD in CA for a stock portfolio

[hazey74]

Good day powerbi users. i am trying a simple dax formula. i have a list of US and CA stocks in a simple table format.  i have an expression  i created with some powerbi forum reading and research.  i am not a programmer.

pb test = sumx('invest_balance1', 'invest_balance1'[Market Value] * if('invest_balance1'[Market] ="CA", 1, [Exchange rate normalized]*[Market Value])).  the US stocks don't calculate correct.

the left column is correct, because in my exch table i have manually entered  exch CA 1:1, and US is the actual daily rates.

the expression i used is :

PB Currency Adj = sumx('invest_balance1', 'invest_balance1'[Market Value] * [Exchange rate normalized])

 my goal is to eliminate the manual entry of CA 1:1 i have in exch table.

any help is appreciated.

thx

[hazey74 · Accepted solution]

i may have got it . thx to AJ1973 and ERB help.  this is what i came up with.

PB Currency Adj =

        sumx(

            invest_balance1,

            invest_balance1[Market Value]

            * if(

                invest_balance1[Market] = "US",

                [Exchange rate normalized],

                1)

    )

[hazey74]

hello, Amine, you want me to upload my actual pbix file, its about 3 mb, not that big.  thx

[aj1973]

Sorry the sample you sent me doesn't help.

Please I need a sample with 

Exchange_Rate_Table

'invest_balance1', 'invest_balance1'[Market Value] and Its primary Key 

These 2 fields where ever they come from and the primary key if they come from another table

Delete all the rest from sample and give what do you need to see as an outcome

Thanks

[aj1973]

It doesn't have to be the same pbix file. A sample out of it is good enough.

[hazey74]

ok,i made a basic file with the exhange table, measures, etc, 

does this forum allow files to be shared?

thx

[aj1973]

No, you can share it via an online Service like Google Drive, Dropbox...

[hazey74]

https://drive.google.com/file/d/1-3veQ8934y6a8aE9o-N4K5IjFWe2M0Kt/view?usp=drive_link

hope this works. thx

[Ashish_Mathur]

Access Denied message.

[hazey74]

aj73 user was helping, he sent me an email i was able to grant access to file. 

[aj1973]

Just sent you an access request

[ERD]

Hi @hazey74 ,

This article will help you to achieve the result: Currency conversion in Power BI reports.

[aj1973]

Hi @hazey74 

Can you provide a Sample pbix please?