Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated column with Excchange rate calculation.

Hello.    I have two tables: VKM1, and Currency.     I'm trying to create calculated column in VKM to multiply Sale ( from VKM1 table ) with Rate ( from Currency table ).    The relationships b...
  • ibarrau's avatar
    ibarrau
    6 years ago

    Hi, if creating a calculated column in VKM1 like this is not working (you only obtain blank values):

    test = RELATED('Currency'[Rate])

    Then check the relationship because it might be not finding matches. Add a table visualization with columns used in the relationship from both tables. Check if they are matching or you have just blank values. This is a usual problem if the columns you use in the relationship don't have the same type or have different texts cases (upper, lower)

     

    Let us know what you find!

    Regards,

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous - Try RELATEDTABLE instead.

 

Otherwise, not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_Deckler 

     

    Thank you for reply. 

     

    In details, 

     

    Two tables. First one "Currency", like below.

     

    CurrencyRate
     USD1,1854
     JPY125,37
     BGN1,9558
     CZK26,097
     DKK7,4508
     GBP0,90265
     HUF344,5
     PLN4,3935
     RON4,8345
     SEK10,2865
     CHF1,077
     ISK160,2
     NOK10,6585

     

    Second one ( VKM1 ) consists of multiple rows, and columns.

     

    Between tables there is relationship may to one ( it is active ).  


     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    I have tried RELATEDTABLE: 

     

    conversion = SUMX(RELATEDTABLE('Currency'),VKM1[Sale]*'Currency'[Rate])
     
    I have tried RELATED ( even basic version inside table VKM1to check it )
     
    test = RELATED('Currency'[Rate])
     
    Both cases as a result I got blank column ( no errrors, no data )
     
    Please advise.  
    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      6 years ago

      Hi, if creating a calculated column in VKM1 like this is not working (you only obtain blank values):

      test = RELATED('Currency'[Rate])

      Then check the relationship because it might be not finding matches. Add a table visualization with columns used in the relationship from both tables. Check if they are matching or you have just blank values. This is a usual problem if the columns you use in the relationship don't have the same type or have different texts cases (upper, lower)

       

      Let us know what you find!

      Regards,

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        ibarrau Greg_Deckler 

         

        Thank you for replies, it lead me to the couse. 

         

        Oryginal exchange rates file is in csv format, in format pasted below. After transforming in Power Query, it still had space at the beginning of each 3 letter currency code.  Removing space solved issue ... .

         

         

        Date, USD, JPY, BGN, CZK, DKK, GBP, HUF, PLN, RON, SEK, CHF, ISK, NOK, HRK, RUB, TRY, AUD, BRL, CAD, CNY, HKD, IDR, ILS, INR, KRW, MXN, MYR, NZD, PHP, SGD, THB, ZAR, EUR
        05 August 2020, 1.1854, 125.37, 1.9558, 26.097, 7.4508, 0.90265, 344.50, 4.3935, 4.8345, 10.2865, 1.0770, 160.20, 10.6585, 7.4683, 86.3692, 8.3311, 1.6415, 6.2311, 1.5703, 8.2277, 9.1871, 17247.57, 4.0397, 88.7090, 1405.74, 26.7125, 4.9698, 1.7809, 58.120, 1.6228, 36.759, 20.4221, 1.00

         

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous - Where are you trying to create the calculated column? If it is in VKM1 table then you need to change your relationship direction to Both