Forum Discussion

Sai_Alkesh's avatar
Sai_Alkesh
Helper II
4 years ago
Solved

Calculated column rows do not return values for all rows

Hi, I have 2 tables - Currency & Sales and they are connected by many to many relationship. I have created a calculated column in Sales table, and it returns value for only one condition, where the...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Sai_Alkesh ，

    Please remove any relationship between currency and sales table, and you can get the expected result...

    Remove relationship

    Best Regards

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Sai_Alkesh , If all rate rae there then you should compare period with period

     

    'SALES'[Sales] *

    DIVIDE(1,

    CALCULATE(

    FIRSTNONBLANK('Currency'[FXRate], TRUE() ),

    FILTER(

    'Currency'

    'Currency'[LCurrency] = 'Sales'[Currency] &&

    'Currency'[FCurrency] = "GBP" &&

    'Currency'[Type] = 'sales'[Type] &&

    'Currency'[Period] = sales[Period])

    )

    )

    • Sai_Alkesh's avatar
      Sai_Alkesh
      Helper II
      4 years ago

      amitchandak - it still gives the same result. The reason i have the period in the filter clause is, 'cos i want to get the inverse value of FX rate which is for that period. Once i get that rate, i want to multiply the sales from Sales table with this derived rate.

      In the calculated column of Sales table, it shows me the result of the derived value, however it only shows where the period in sales table is '20214'

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Sai_Alkesh , do you have rates other than 20214.

         

        Can share a little bigger sample to test