topic Re: Divide a value by another value in a related table in DAX Commands and Tips

Divide a value by another value in a related table

KF-Hornsby — Sun, 02 May 2021 12:11:24 GMT

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

Re: Divide a value by another value in a related table

Anonymous — Sun, 02 May 2021 13:41:12 GMT

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] )

Re: Divide a value by another value in a related table

Jihwan_Kim — Sun, 02 May 2021 15:13:30 GMT

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

Re: Divide a value by another value in a related table

KF-Hornsby — Sun, 02 May 2021 16:45:45 GMT

Re: Divide a value by another value in a related table

KF-Hornsby — Sun, 02 May 2021 16:51:41 GMT

Re: Divide a value by another value in a related table

KF-Hornsby — Sun, 02 May 2021 16:56:53 GMT

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).

Re: Divide a value by another value in a related table

Rémi-Quentin — Thu, 03 Jun 2021 00:16:20 GMT

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

Re: Divide a value by another value in a related table

KF-Hornsby — Mon, 04 Mar 2024 22:36:08 GMT

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.