Forum Discussion

KF-Hornsby's avatar
KF-Hornsby
Frequent Visitor
5 years ago
Solved

Divide a value by another value in a related table

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's avatar
    KF-Hornsby
    2 years ago

    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.

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
    KF-Hornsby
    Frequent Visitor
    5 years ago

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

     

     

  • Rémi-Quentin's avatar
    Rémi-Quentin
    Regular Visitor
    5 years ago

    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's avatar
    KF-Hornsby
    Frequent Visitor
    2 years ago

    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.