Divide a value by another value in a related table

[KF-Hornsby]

Good afternoon, 

I'm trying to divide a sales value by an FX rate held in a different table.  I have a Sales Facts table with a many to one join to an FX Rates table (joined on currency code).  I need to calculate the sales in GBP.  Idealy, this would be as a calculated column as I need to re-use this elsewhere.

I've tried

Total Sales CY GBP = divide('Sales Facts'[Sales Value CY], related('FX Rates'[FXRate YTD]))

but I get the error 

The relationship DOES exist.

Any help greatly appreachiated.

Dave

[KF-Hornsby · Accepted solution]

It's a long time since I posted this so I think the answer was something along the lines of one of the tables being import and the other being direct query.  Once the whole model was set to import the issue stopped being a problem.

[Rémi-Quentin]

hi everyone,
i'm really interested in this topic as i seem to have the exact same issue with my measure.

i'm wondering if it has something to do with the main table (in the sumx) to be a direct query table ?

as i've tried it with other table that are in import mode and the error "this column either doesn't exist or ..." doesn't show up and it seems to calculate my measure.

i hope we'll soon have something to fix this 🙂

thanks,

Rémi

[KF-Hornsby]

Is there anything special about this relationship what with it having the " <> " at either end?  Is it just representative of it joining two different data sources (a table imported/dual from a data flow and a direct query to a SQL server).

[Jihwan_Kim]

Hi, @KF-Hornsby 

Please try something like below.

total sales CY GBP =
SUMX (
'Sales Facts',
'Sales Facts'[Sales Value CY] / RELATED ( 'FX Rates'[FXRate YTD] )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[KF-Hornsby]

[Anonymous]

Just a couple quick thoughts make sure data format are the same in both side.  You can also do a lookup function rather then related.  Lookup doesn't need a relationship between the 2 tables. 

example 

divide([sales] , lookup(fxrates[fxrate],fxrates[currcode],salesfact[currency code] )

[KF-Hornsby]